This topic describes how to use the secondary index feature as an enhancement of ApsaraDB for ClickHouse.

Background information

The official open source ClickHouse does not provide the secondary index feature. The following secondary index-related features are enhancements of ApsaraDB for ClickHouse and are available only in ApsaraDB for ClickHouse v20.3 and later. Secondary indexes in ApsaraDB for ClickHouse are different from indexes in the open source ClickHouse, nor do they address the same type of issues. Secondary indexes are most commonly used to accelerate point queries based on the equivalence conditions on non-sort keys.

Syntax of secondary indexes

The following statement provides an example on how to define secondary indexes when a table is created:

CREATE TABLE index_test 
(
  id UInt64, 
  d DateTime, 
  x UInt64,
  y UInt64, 
  tag String,
  KEY tag_idx tag TYPE range, -- Define a single-column index.
  KEY d_idx toStartOfHour(d) TYPE range, -- Define an expression index.
  KEY combo_idx (toStartOfHour(d), x, y) TYPE range, -- Define a multi-column index.
) ENGINE = MergeTree() ORDER BY id;

The following data definition language (DDL) statements provide examples on how to modify secondary indexes:

-- Delete an index definition.
Alter table index_test DROP KEY tag_idx;
--Create an index definition.
Alter table index_test ADD KEY tag_idx tag TYPE range;
-- Delete an index file from a data partition.
Alter table index_test CLEAR KEY tag_idx tag in partition partition_expr;
-- Rebuild an index file in a data partition.
Alter table index_test MATERIALIZE KEY tag_idx tag in partition partition_expr;

Features of secondary indexes

Secondary indexes in ApsaraDB for ClickHouse support the basic set operations of intersection, union, and difference on multi-index columns. The secondary indexes have the following generic features:

  • Multi-column indexes and expression indexes
  • Function pushdown
  • Pushdown in Set clauses
  • High compression ratio (similar performance to Lucene 8.7 in terms of index file compression)
  • Vectorized indexing (four times faster than Lucene 8.7)

Multi-column indexes are intended to reduce index merges in a specific query pattern. You can create multi-column indexes for workloads that require particularly high queries per second (QPS) to maximize the retrieval performance. Expression indexes make it easier for you to adjust the retrieval granularity. Expression indexes can be used in the following typical scenarios:

  • The time column in a secondary index is filtered only on an hourly basis in search conditions. In this case, you can create an index for the toStartOfHour(time) expression, which can accelerate index creation to some extent. In addition, filter conditions on the time column can be automatically transformed into a means to push down the index.
  • The id column in a secondary index is composed of universally unique identifiers (UUIDs). A UUID is a string sequence that is statistically guaranteed to be distinct. If you create an index on the id column, the index file can be extremely large in size. In this case, you can use a prefix function to extract parts of a UUID to create an index. For example, prefix8(id) extracts the first 8 bytes of a UUID as an index prefix. Additional examples include prefix4 and prefix16. prefixUTF4, prefixUTF8, and prefixUTF16 are used to extract UTF-encoded content.

After you create an index on an expression, query conditions on the source column can also push down the index without the need to rewrite queries. In the same way, after you create an index on the source column, the optimizer can also push down the index without any issues when an expression is added for the column in the filter conditions.

Pushdown in Set clauses is a typical scenario of associative search. A common scenario is that a wide table that records user attributes and a table that records user behaviors are used. To search for specific users, you must aggregate and filter out the user IDs that meet specific conditions from the behavior table, and then use user IDs to retrieve detailed records from the attribute table. In such subquery scenarios, ApsaraDB for ClickHouse can also automatically push down secondary indexes to accelerate queries.

Secondary index performance

The following section provides the test results of ApsaraDB for ClickHouse against Lucene 8.7. The test results compare the performance and compression ratio of secondary indexes with those of inverted indexes and BKD trees. Statistics for the indexing duration are collected from single-threaded jobs.

Scenario 1: UUID strings
CREATE TABLE string_index_test (
 `C_KEY` String,  
 KEY C_KEY_IDX C_KEY Type range
) ...
INSERT INTO string_index_test 
select substringUTF8(cast (generateUUIDv4() as String), 1, 16) 
from system.numbers limit 100000000;

The following table lists the test results.

Test object Indexing duration Index file size Data file size
ApsaraDB for ClickHouse 97.2s 1.3 GB 1.5 GB
Lucene 487.255s 1.3 GB N/A
Scenario 2: enumerated strings
CREATE TABLE string_index_test (
 `C_KEY` LowCardinality(String),  
 KEY C_KEY_IDX C_KEY Type range
) ...
INSERT INTO string_index_test 
select cast((10000000 + rand(number) % 4000) as String) 
from system.numbers limit 100000000;
Test object Indexing duration Index file size Data file size
ApsaraDB for ClickHouse 25.5s 187 MB 193 MB
Lucene 45.513s 187 MB N/A
Scenario 3: random numeric values
CREATE TABLE long_index_test (
  `C_KEY` UInt64,  
  KEY C_KEY_IDX C_KEY Type range
) ...
INSERT INTO long_index_test 
select rand(number) % 100000000 
from system.numbers limit 100000000;
Test object Indexing duration Index file size Data file size
ApsaraDB for ClickHouse 34.2s 615 MB 519 MB
Lucene 81.971s 482 MB N/A
Scenario 4: enumerated numeric values
CREATE TABLE int_index_test (
  `C_KEY` UInt32,  
  KEY C_KEY_IDX C_KEY Type range
) ...
INSERT INTO int_index_test 
select rand(number) % 1000 
from system.numbers limit 100000000;
Test object Indexing duration Index file size Data file size
ApsaraDB for ClickHouse 12.2s 163 MB 275 MB
Lucene 77.999s 184 MB N/A

Performance of secondary indexes in equivalence queries

Test environment: a memory optimized Elastic Compute Service (ECS) instance that is equipped with 32 cores, 128 GB memory, and a PL1 enhanced SSD (ESSD) of 1 TB.

Test data: a total of 13E data rows. The table has the following schema:

CREATE TABLE point_search_test (
 `PRI_KEY` String,  
 `SED_KEY` String,  
 `INT_0` UInt32, 
 `INT_1` UInt32, 
 `INT_2` UInt32, 
 `INT_3` UInt32, 
 `INT_4` UInt32, 
 `LONG_0` UInt64, 
 `LONG_1` UInt64, 
 `LONG_2` UInt64, 
 `LONG_3` UInt64, 
 `LONG_4` UInt64, 
 `STR_0` String, 
 `STR_1` String, 
 `STR_2` String, 
 `STR_3` String, 
 `STR_4` String, 
 `FIXSTR_0` FixedString(16), 
 `FIXSTR_1` FixedString(16), 
 `FIXSTR_2` FixedString(16), 
 `FIXSTR_3` FixedString(16), 
 `FIXSTR_4` FixedString(16), 
 KEY SED_KEY_IDX SED_KEY Type range
) ...

The following table lists the numbers of equivalence queries per second (QPS) that are implemented by using secondary indexes.

Test object select * select INT_0 select LONG_0 select STR_0 select FIXSTR_0
QPS on cold start 600 3,200 3,700 3,600 3,700
QPS after warm-up 4,900 27,000 27,000 26,000 26,000

Recommended configurations for optimal performance of secondary indexes

If you have high requirements for secondary index performance, we recommend that you purchase an ECS instance that is equipped with 32 cores and 128 GB memory and has PL2 ESSDs attached.

Parameter settings at the instance level: Set min_compress_block_size to 4096 and max_compress_block_size to 8192.

Parameter settings at the MergeTree table level: Set the min_bytes_for_compact_part parameter to Compact Format as a preferred choice.