Configure Clustering Keys to Accelerate Range Queries - Hologres

A Clustering Key physically sorts data within each file based on the columns you specify. Queries that filter on those columns can skip irrelevant data blocks entirely, which significantly reduces scan time for range and point queries. Use a Clustering Key when queries run frequently against large tables with range or equality filters on a predictable set of columns.

Unlike clustered indexes in MySQL or SQL Server, a Clustering Key sorts data within individual files — not across the entire table. Running ORDER BY on a Clustering Key still has some overhead because the data sorted within files requires an additional merge step in memory.

How it works

Hologres stores table data in multiple files. Without a Clustering Key, a filter query must scan every data block. With a Clustering Key, data is physically sorted within each file, so the query engine can skip blocks that fall outside the filter range using the Cluster Filter operator.

The Clustering Key follows a left-prefix matching principle. Given a Clustering Key on columns (a, b, c):

Query filter	Clustering Key hit
`WHERE a = 1`	Full hit on `a`
`WHERE a = 1 AND b > 2`	Full hit on `a, b`
`WHERE a = 1 AND b = 2 AND c > 3`	Full hit on `a, b, c`
`WHERE a = 1 AND c > 3`	Partial hit — only `a`; `c` uses a regular filter
`WHERE b = 1 AND c > 3`	No hit — left prefix `a` is missing

To confirm that a query hits the Clustering Key, check the execution plan with EXPLAIN. The plan must contain the Cluster Filter operator.

Logical layout

Consider a table with columns Name, Date, and Class:

Clustering Key = Date: data is sorted by date across all rows in each file.
Clustering Key = Class, Date: data is first sorted by class, then by date within each class.

Physical storage layout

Set a Clustering Key

Specify the Clustering Key at table creation time. You cannot change it later without recreating the table and reimporting the data.

Syntax for Hologres V2.1 and later:

CREATE TABLE <table_name> (...) WITH (clustering_key = '[<columnName>[,...]]');

Syntax for all versions:

BEGIN;
CREATE TABLE <table_name> (...);
CALL set_table_property('<table_name>', 'clustering_key', '[<columnName>{:asc} [,...]]');
COMMIT;

Parameter	Description
`table_name`	Name of the table
`columnName`	Column(s) to include in the Clustering Key

Example — set a Clustering Key on `uid, class, date`:

Syntax for V2.1 and later:

CREATE TABLE clustering_test (
    uid int NOT NULL,
    name text NOT NULL,
    class text NOT NULL,
    date text NOT NULL,
    PRIMARY KEY (uid)
)
WITH (
    clustering_key = 'uid,class,date'
);

INSERT INTO clustering_test VALUES
(1,'Zhang San','1','2022-10-19'),
(2,'Li Si','3','2022-10-19'),
(3,'Wang Wu','2','2022-10-20'),
(4,'Zhao Liu','2','2022-10-20'),
(5,'Sun Qi','2','2022-10-18'),
(6,'Zhou Ba','3','2022-10-17'),
(7,'Wu Jiu','3','2022-10-20');

Syntax for all versions:

BEGIN;
CREATE TABLE clustering_test (
  uid int NOT NULL,
  name text NOT NULL,
  class text NOT NULL,
  date text NOT NULL,
  PRIMARY KEY (uid)
);
CALL set_table_property('clustering_test', 'clustering_key', 'uid,class,date');
COMMIT;

INSERT INTO clustering_test VALUES
(1,'Zhang San','1','2022-10-19'),
(2,'Li Si','3','2022-10-19'),
(3,'Wang Wu','2','2022-10-20'),
(4,'Zhao Liu','2','2022-10-20'),
(5,'Sun Qi','2','2022-10-18'),
(6,'Zhou Ba','3','2022-10-17'),
(7,'Wu Jiu','3','2022-10-20');

Verify with execution plans:

Query on uid only — hits the Clustering Key:

SELECT * FROM clustering_test WHERE uid > '3';

Query on uid, class — hits the Clustering Key:

SELECT * FROM clustering_test WHERE uid = '3' AND class > '1';

Query on uid, class, date — hits the Clustering Key:

SELECT * FROM clustering_test WHERE uid = '3' AND class = '2' AND date > '2022-10-17';

Query on uid, date — only uid hits; date uses a regular filter:

SELECT * FROM clustering_test WHERE uid = '3' AND date > '2022-10-17';

Execution plan: uid and date (partial hit)

Query on class, date only — no hit (left prefix missing):

SELECT * FROM clustering_test WHERE class = '2' AND date > '2022-10-17';

Choose columns for a Clustering Key

Column order matters. Columns listed first have higher sorting priority and are most likely to be hit by queries. The Clustering Key follows the left-prefix matching principle — a query that skips the first column cannot use the key at all.

Limit the Clustering Key to two columns or fewer. Because the Clustering Key follows the left-prefix matching principle, adding more columns narrows the range of queries that can benefit from it and limits its applicable scenarios.

Combine with a Bitmap Column for optimal point query performance. A Bitmap Column handles equality lookups on low-cardinality columns, while the Clustering Key handles range filtering.

Set the sort order

The default sort order is ascending (ASC). Append :asc or :desc after the column name to set the direction explicitly.

Important

Versions earlier than V2.1 do not support DESC for Clustering Key index building. Setting a column to DESC in those versions prevents the Clustering Key from being hit and degrades query performance.

Starting from V2.1, enable descending sort by running the following Grand Unified Configuration (GUC) parameter before your DDL statement:

set hg_experimental_optimizer_enable_variable_length_desc_ck_filter = on;

DESC support is limited to fields of data types such as Text, Char, Varchar, Bytea, and Int. Fields of other data types do not currently support DESC.

Example — mixed sort order (V2.1 and later):

CREATE TABLE tbl (
    a int NOT NULL,
    b text NOT NULL
)
WITH (
    clustering_key = 'a:desc,b:asc'
);

Syntax for all versions:

BEGIN;
CREATE TABLE tbl (
  a int NOT NULL,
  b text NOT NULL
);
CALL set_table_property('tbl', 'clustering_key', 'a:desc,b:asc');
COMMIT;

Usage examples

Example 1: Queries that hit the Clustering Key

Clustering Key on col1, col2:

Syntax for V2.1 and later:

CREATE TABLE table1 (
    col1 int NOT NULL,
    col2 text NOT NULL,
    col3 text NOT NULL,
    col4 text NOT NULL
)
WITH (
    clustering_key = 'col1,col2'
);

-- Can be accelerated: equality filter on col1
SELECT * FROM table1 WHERE col1 = 'abc';

-- Can be accelerated: range filter on col1
SELECT * FROM table1 WHERE col1 > 'xxx' AND col1 < 'abc';

-- Can be accelerated: IN filter on col1
SELECT * FROM table1 WHERE col1 IN ('abc', 'def');

-- Can be accelerated: equality filter on col1 and col2
SELECT * FROM table1 WHERE col1 = 'abc' AND col2 = 'def';

-- Cannot be accelerated: col1 (the left prefix) is missing
SELECT col1, col4 FROM table1 WHERE col2 = 'def';

Syntax for all versions:

BEGIN;
CREATE TABLE table1 (
  col1 int NOT NULL,
  col2 text NOT NULL,
  col3 text NOT NULL,
  col4 text NOT NULL
);
CALL set_table_property('table1', 'clustering_key', 'col1,col2');
COMMIT;

-- Can be accelerated: equality filter on col1
SELECT * FROM table1 WHERE col1 = 'abc';

-- Can be accelerated: range filter on col1
SELECT * FROM table1 WHERE col1 > 'xxx' AND col1 < 'abc';

-- Can be accelerated: IN filter on col1
SELECT * FROM table1 WHERE col1 IN ('abc', 'def');

-- Can be accelerated: equality filter on col1 and col2
SELECT * FROM table1 WHERE col1 = 'abc' AND col2 = 'def';

-- Cannot be accelerated: col1 (the left prefix) is missing
SELECT col1, col4 FROM table1 WHERE col2 = 'def';

Limitations

Modifying a Clustering Key requires recreating the table. Drop the table, recreate it with the new Clustering Key definition, and reimport the data.
Columns must be NOT NULL. Versions V1.3.20 through V1.3.27 allowed nullable Clustering Keys. Starting from V1.3.28, nullable Clustering Keys are not supported because they can cause data correctness issues. To use a nullable Clustering Key (not recommended), add the following parameter before your SQL statement:
```
set hg_experimental_enable_nullable_clustering_key = true;
```
Unsupported column types. The following data types cannot be used in a Clustering Key: Float, Float4, Float8, Double, Decimal (Numeric), JSON, JSONB, Bit, Varbit, Money, Time With Time Zone, and other complex data types.
Descending sort requires V2.1. Versions earlier than V2.1 do not support DESC sort order for Clustering Key index building. See Set the sort order for details.
Column-oriented tables have no default Clustering Key. Explicitly specify the Clustering Key based on your query patterns.
Row-oriented tables default to the primary key as the Clustering Key. This default was not set in versions earlier than V0.9. If you set a Clustering Key that differs from the primary key, Hologres creates two sorted orders — one for the primary key and one for the Clustering Key — which causes data redundancy.

Only one Clustering Key per table. Set all columns in a single clustering_key property. Calling set_table_property for clustering_key more than once on the same table is not supported. Correct:

-- V2.1 and later
CREATE TABLE tbl (
    a int NOT NULL,
    b text NOT NULL
)
WITH (
    clustering_key = 'a,b'
);

-- All versions
BEGIN;
CREATE TABLE tbl (a int NOT NULL, b text NOT NULL);
CALL set_table_property('tbl', 'clustering_key', 'a,b');
COMMIT;

Incorrect:

-- V2.1 and later: duplicate clustering_key property
CREATE TABLE tbl (
    a int NOT NULL,
    b text NOT NULL
)
WITH (
    clustering_key = 'a',
    clustering_key = 'b'
);

-- All versions: set_table_property called twice
BEGIN;
CREATE TABLE tbl (a int NOT NULL, b text NOT NULL);
CALL set_table_property('tbl', 'clustering_key', 'a');
CALL set_table_property('tbl', 'clustering_key', 'b');
COMMIT;

Advanced tuning

ORDER BY on Clustering Keys (V1.3 and later)

Because data is sorted within each file, Hologres V1.3 introduced an optimization that lets the query optimizer use this sorted order when generating execution plans. The optimizer can maintain the sort order across Shuffle nodes using a pipelined multi-way merge, which avoids a full re-sort of large data sets.

Before V1.3, the optimizer could not use the Clustering Key's sorted order. Data passing through a Shuffle node was not guaranteed to remain sorted, resulting in a full re-sort (a Sort operator) at the top of the plan and higher computation time.

Example:

Table DDL (V2.1 and later):

DROP TABLE IF EXISTS test_use_sort_info_of_clustering_keys;

CREATE TABLE test_use_sort_info_of_clustering_keys (
    a int NOT NULL,
    b int NOT NULL,
    c text
)
WITH (
    distribution_key = 'a',
    clustering_key = 'a,b'
);

INSERT INTO test_use_sort_info_of_clustering_keys SELECT i%500, i%100, i::text FROM generate_series(1, 1000) AS s(i);

ANALYZE test_use_sort_info_of_clustering_keys;

Table DDL (all versions):

DROP TABLE IF EXISTS test_use_sort_info_of_clustering_keys;
BEGIN;
CREATE TABLE test_use_sort_info_of_clustering_keys
(
    a int NOT NULL,
    b int NOT NULL,
    c text
);
CALL set_table_property('test_use_sort_info_of_clustering_keys', 'distribution_key', 'a');
CALL set_table_property('test_use_sort_info_of_clustering_keys', 'clustering_key', 'a,b');
COMMIT;

INSERT INTO test_use_sort_info_of_clustering_keys SELECT i%500, i%100, i::text FROM generate_series(1, 1000) AS s(i);

ANALYZE test_use_sort_info_of_clustering_keys;

Query:

EXPLAIN SELECT * FROM test_use_sort_info_of_clustering_keys WHERE a > 100 ORDER BY a, b;

Execution plan before V1.3 (V1.1):

 Sort  (cost=0.00..0.00 rows=797 width=11)
   ->  Gather  (cost=0.00..2.48 rows=797 width=11)
         Sort Key: a, b
         ->  Sort  (cost=0.00..2.44 rows=797 width=11)
               Sort Key: a, b
               ->  Exchange (Gather Exchange)  (cost=0.00..1.11 rows=797 width=11)
                     ->  Decode  (cost=0.00..1.11 rows=797 width=11)
                           ->  Index Scan using holo_index:[1] on test_use_sort_info_of_clustering_keys  (cost=0.00..1.00 rows=797 width=11)
                                 Cluster Filter: (a > 100)

Execution plan in V1.3:

 Gather  (cost=0.00..1.15 rows=797 width=11)
   Merge Key: a, b
   ->  Exchange (Gather Exchange)  (cost=0.00..1.11 rows=797 width=11)
         Merge Key: a, b
         ->  Decode  (cost=0.00..1.11 rows=797 width=11)
               ->  Index Scan using holo_index:[1] on test_use_sort_info_of_clustering_keys  (cost=0.00..1.01 rows=797 width=11)
                     Order by: a, b
                     Cluster Filter: (a > 100)

In V1.3, the plan uses Merge Key instead of Sort, eliminating the top-level sort and enabling fully pipelined execution. The execution plan comparison shows that V1.3 generates a GroupAggregate, which is less complex and performs better than a HashAggregate.

When the table is not filtered by the Clustering Key columns, the optimizer uses a Sequential Scan (SeqScan) by default, not an Index Scan. Only an Index Scan uses the sorted property of the Clustering Key. The optimizer does not always generate a plan based on the Clustering Key's sorted order — using sort order has overhead because data sorted within files still requires merging in memory.

JOIN on Clustering Keys — Sort Merge Join (Beta, V1.3 and later)

V1.3 introduced the Sort Merge Join type. This join type ensures that the execution plan can use the sorted order of the Clustering Key to reduce computation and improve performance. It is pipelined and avoids out-of-memory (OOM) issues that can occur when the hash side of a Hash Join is too large to fit in memory.

This feature is disabled by default. Enable it before your query:

set hg_experimental_enable_sort_merge_join = on;

Example:

Table DDL (V2.1 and later):

DROP TABLE IF EXISTS test_use_sort_info_of_clustering_keys1;
CREATE TABLE test_use_sort_info_of_clustering_keys1 (
    a int,
    b int,
    c text
)
WITH (
    distribution_key = 'a',
    clustering_key = 'a,b'
);

INSERT INTO test_use_sort_info_of_clustering_keys1 SELECT i % 500, i % 100, i::text FROM generate_series(1, 10000) AS s(i);
ANALYZE test_use_sort_info_of_clustering_keys1;

DROP TABLE IF EXISTS test_use_sort_info_of_clustering_keys2;
CREATE TABLE test_use_sort_info_of_clustering_keys2 (
    a int,
    b int,
    c text
)
WITH (
    distribution_key = 'a',
    clustering_key = 'a,b'
);

INSERT INTO test_use_sort_info_of_clustering_keys2 SELECT i % 600, i % 200, i::text FROM generate_series(1, 10000) AS s(i);
ANALYZE test_use_sort_info_of_clustering_keys2;

Table DDL (all versions):

DROP TABLE IF EXISTS test_use_sort_info_of_clustering_keys1;
BEGIN;
CREATE TABLE test_use_sort_info_of_clustering_keys1
(
  a int,
  b int,
  c text
);
CALL set_table_property('test_use_sort_info_of_clustering_keys1', 'distribution_key', 'a');
CALL set_table_property('test_use_sort_info_of_clustering_keys1', 'clustering_key', 'a,b');
COMMIT;
INSERT INTO test_use_sort_info_of_clustering_keys1 SELECT i%500, i%100, i::text FROM generate_series(1, 10000) AS s(i);
ANALYZE test_use_sort_info_of_clustering_keys1;

DROP TABLE IF EXISTS test_use_sort_info_of_clustering_keys2;
BEGIN;
CREATE TABLE test_use_sort_info_of_clustering_keys2
(
  a int,
  b int,
  c text
);
CALL set_table_property('test_use_sort_info_of_clustering_keys2', 'distribution_key', 'a');
CALL set_table_property('test_use_sort_info_of_clustering_keys2', 'clustering_key', 'a,b');
COMMIT;
INSERT INTO test_use_sort_info_of_clustering_keys2 SELECT i%600, i%200, i::text FROM generate_series(1, 10000) AS s(i);
ANALYZE test_use_sort_info_of_clustering_keys2;

Query:

EXPLAIN SELECT * FROM test_use_sort_info_of_clustering_keys1 a
JOIN test_use_sort_info_of_clustering_keys2 b
  ON a.a = b.a AND a.b = b.b
WHERE a.a > 100 AND b.a < 300;

Execution plan before V1.3 (V1.1):

 Gather  (cost=0.00..3.09 rows=4762 width=24)
   ->  Hash Join  (cost=0.00..2.67 rows=4762 width=24)
         Hash Cond: ((test_use_sort_info_of_clustering_keys1.a = test_use_sort_info_of_clustering_keys2.a) AND (test_use_sort_info_of_clustering_keys1.b = test_use_sort_info_of_clustering_keys2.b))
         ->  Exchange (Gather Exchange)  (cost=0.00..1.14 rows=3993 width=12)
               ->  Decode  (cost=0.00..1.14 rows=3993 width=12)
                     ->  Index Scan using holo_index:[1] on test_use_sort_info_of_clustering_keys1  (cost=0.00..1.01 rows=3993 width=12)
                           Cluster Filter: ((a > 100) AND (a < 300))
         ->  Hash  (cost=1.13..1.13 rows=3386 width=12)
               ->  Exchange (Gather Exchange)  (cost=0.00..1.13 rows=3386 width=12)
                     ->  Decode  (cost=0.00..1.13 rows=3386 width=12)
                           ->  Index Scan using holo_index:[1] on test_use_sort_info_of_clustering_keys2  (cost=0.00..1.01 rows=3386 width=12)
                                 Cluster Filter: ((a > 100) AND (a < 300))

Execution plan in V1.3 (with Sort Merge Join enabled):

  Gather  (cost=0.00..2.88 rows=4762 width=24)
   ->  Merge Join  (cost=0.00..2.46 rows=4762 width=24)
         Merge Cond: ((test_use_sort_info_of_clustering_keys2.a = test_use_sort_info_of_clustering_keys1.a) AND (test_use_sort_info_of_clustering_keys2.b = test_use_sort_info_of_clustering_keys1.b))
         ->  Exchange (Gather Exchange)  (cost=0.00..1.14 rows=3386 width=12)
               Merge Key: test_use_sort_info_of_clustering_keys2.a, test_use_sort_info_of_clustering_keys2.b
               ->  Decode  (cost=0.00..1.14 rows=3386 width=12)
                     ->  Index Scan using holo_index:[1] on test_use_sort_info_of_clustering_keys2  (cost=0.00..1.01 rows=3386 width=12)
                           Order by: test_use_sort_info_of_clustering_keys2.a, test_use_sort_info_of_clustering_keys2.b
                           Cluster Filter: ((a > 100) AND (a < 300))
         ->  Exchange (Gather Exchange)  (cost=0.00..1.14 rows=3993 width=12)
               Merge Key: test_use_sort_info_of_clustering_keys1.a, test_use_sort_info_of_clustering_keys1.b
               ->  Decode  (cost=0.00..1.14 rows=3993 width=12)
                     ->  Index Scan using holo_index:[1] on test_use_sort_info_of_clustering_keys1  (cost=0.00..1.01 rows=3993 width=12)
                           Order by: test_use_sort_info_of_clustering_keys1.a, test_use_sort_info_of_clustering_keys1.b
                           Cluster Filter: ((a > 100) AND (a < 300))

In V1.3, the optimizer performs a merge sort within each shard and directly executes a Sort Merge Join, enabling fully pipelined execution. The Hash Join is replaced, eliminating the risk of OOM when the hash-side table is large.

The same caveats apply as with ORDER BY optimization — the optimizer uses SeqScan when the table is not filtered by the Clustering Key, and does not always choose the Clustering Key sort order.

What's next

For Data Definition Language (DDL) statements related to Hologres internal tables: