Clustering key

Introduction

-- Syntax supported by Hologres V2.1 and later
CREATE TABLE <table_name> (...) WITH (clustering_key = '[<columnName>[,...]]');
-- Syntax supported by all versions
BEGIN;
CREATE TABLE <table_name> (...);
CALL set_table_property('<table_name>', 'clustering_key', '[<columnName>{:asc} [,...]]');
COMMIT;

Usage recommendations

• A clustering key is ideal for point and range queries. It significantly improves performance for filter operations, such as queries with WHERE a = 1 or WHERE a > 1 AND a < 5. For optimal point query performance, you can define both a clustering key and a bitmap index on the same columns.

• A clustering key follows the leftmost matching principle. Therefore, we recommend that you include no more than two columns in a clustering key to ensure it applies to a wide range of queries. The order of columns matters: columns listed first have a higher sorting priority than columns listed later.

• When you specify a Clustering Key field, you can add :asc after the field name to specify the sort order for the index. The default sort order is asc (ascending). Hologres versions earlier than V2.1 do not support setting the sort order to descending (desc). In these versions, if the sort order was set to descending, the Clustering Key could not be hit, which resulted in poor query performance. Starting from V2.1, you can set the Clustering Key to desc after you enable a specific GUC parameter. However, this feature is supported only for fields of data types such as Text, Char, Varchar, Bytea, and Int. Setting the Clustering Key to desc is not yet supported for fields of other data types.

  set hg_experimental_optimizer_enable_variable_length_desc_ck_filter = on;

• For a row-oriented table, the clustering key defaults to the primary key. In versions earlier than Hologres V0.9, no clustering key was set by default. If you define a clustering key that is different from the primary key, Hologres creates two sorted copies of the data, one for the primary key and one for the clustering key. This causes data redundancy.

Limits

• To modify a clustering key, you must create a new table and import the data.

• The columns specified in a clustering key must be NOT NULL. Hologres versions from V1.3.20 to V1.3.27 supported nullable columns in a clustering key, but this is no longer supported starting from V1.3.28. A nullable clustering key can affect data correctness. If your business requires a nullable clustering key, you can enable it by running the following command before your SQL statement.

  set hg_experimental_enable_nullable_clustering_key = true;

• Columns with the following data types cannot be used in a clustering key: Float, Float4, Float8, Double, Decimal(Numeric), Json, Jsonb, Bit, Varbit, Money, and Time With Time Zone, or other complex data types.

• In Hologres versions earlier than V2.1, you cannot set the sort order for an index to descending (desc). If the sort order is set to descending, the clustering key cannot be hit, which results in poor query performance. Starting from V2.1, you can enable the following GUC to set the clustering key to desc. However, this feature is supported only for fields of data types such as Text, Char, Varchar, Bytea, and Int. You cannot set the clustering key to desc for fields of other data types.

  set hg_experimental_optimizer_enable_variable_length_desc_ck_filter = on;

• For a column-oriented table, no clustering key is defined by default. You must explicitly specify one based on your use case.

• In Hologres, you can set only one clustering key for each table. This means that when you create a table, you can run the call command only once, not multiple times. For example:

  • Syntax supported by Hologres V2.1 and later:

    -- Correct usage
    CREATE TABLE tbl (
        a int NOT NULL,
        b text NOT NULL
    )
    WITH (
        clustering_key = 'a,b'
    );
    -- Incorrect usage
    CREATE TABLE tbl (
        a int NOT NULL,
        b text NOT NULL
    )
    WITH (
        clustering_key = 'a',
        clustering_key = 'b'
    );

  • Syntax supported by all versions:

    -- Correct usage
    BEGIN;
    CREATE TABLE tbl (a int NOT NULL, b text NOT NULL);
    CALL set_table_property('tbl', 'clustering_key', 'a,b');
    COMMIT;
    -- Incorrect usage
    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;

How it works

A clustering key physically sorts data within files. By default, the data is sorted in ascending (asc) order. The following sections explain the concepts of logical and physical layouts for a clustering key.

• Logical layout

  Queries that use a clustering key follow the leftmost matching principle. If a query's filter conditions do not match the prefix of the clustering key columns, the clustering key cannot accelerate the query. The following example illustrates the logical layout of a clustering key in Hologres.

  Assume you have a table with the columns Name, Date, and Class.

  • If you set the clustering key to (Date), the data in the table is sorted by the Date column.

  • If you set the clustering key to (Class, Date), the data is first sorted by the Class column, and then by the Date column within each class.

  The choice of clustering key columns determines the final sorted layout of the data.

• Physical storage layout

  The physical storage layout of a clustering key groups related data together.

Examples

Advanced tuning

Unlike the clustering key in traditional databases such as MySQL or SQL Server, sorting in Hologres is performed only within files, not across the entire table. Therefore, performing an ORDER BY operation on the Clustering Key still has a performance cost.

Starting from V1.3, Hologres introduced significant performance optimizations for clustering key use cases. These optimizations provide better performance in the following two scenarios. If your Hologres instance is on a version earlier than 1.3, see Troubleshooting: Upgrade preparation errors or contact us for support by following the instructions in How to get online support?.

• Use ORDER BY on clustering keys

  In Hologres, data within a file is sorted according to the defined clustering keys. In versions earlier than V1.3, the optimizer could not use the sorted property of clustering keys to generate an optimal execution plan. Additionally, data order could not be guaranteed after passing through a shuffle node due to multi-way merging. This often led to higher computational load and longer query times. Hologres V1.3 optimizes this process. The optimizer can now generate an execution plan that uses the sorted property of clustering keys and preserves the data order across shuffles, which improves query performance. However, note the following points:

  • If a query does not filter on the clustering keys of a table, it defaults to a SeqScan instead of an IndexScan. Only an IndexScan uses the sorted property of clustering keys.

  • The optimizer does not always choose an execution plan that relies on the sort order of the clustering key, as this approach has some overhead. For example, data that is sorted within files might still require additional sorting in memory.

  Example:

  • Syntax supported by Hologres 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;

    Syntax supported by 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 statement:

    explain select * from test_use_sort_info_of_clustering_keys where a > 100  order by a, b;

  • Execution plan comparison

    • The following code shows the execution plan in a version earlier than V1.3, such as V1.1. You can view the plan by running the EXPLAIN statement.

       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)

    • The following code shows the execution plan in Hologres 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)

    The execution plan for V1.3 is more efficient. It uses the sorted property of the clustering key to directly merge the output. This creates a pipelined execution and prevents slow sorting operations when you work with large datasets. As shown in the comparison, the V1.3 plan replaces the Sort operator with a Merge Key, which indicates a more efficient merge-based process.

• Use JOIN on clustering keys (Beta)

  Hologres V1.3 introduced the Merge Join operator, which allows the optimizer to leverage the sorted nature of clustering keys, reduce computational load, and improve performance. However, note the following points:

  • This feature is in beta and is disabled by default. To use it, you must enable the following parameter before you execute your query.

    -- Enable merge join
    set hg_experimental_enable_sort_merge_join=on;

  • If a query does not filter on the clustering keys of a table, it defaults to a SeqScan instead of an IndexScan. Only an IndexScan uses the sorted property of clustering keys.

  • The optimizer does not always generate an execution plan based on the sorted order of clustering keys because using this property has some costs. For example, data that is sorted within files might still require additional sorting in memory.

  Example:

  • Syntax supported by Hologres 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;

    Syntax supported by 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 statement:

    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 comparison

    • The following code shows the execution plan in a version earlier than V1.3, such as 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))

    • The following code shows the execution plan in Hologres V1.3.

        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))

    The V1.3 plan uses a Merge Join by leveraging the sorted nature of the clustering key. It performs a sort-merge within each shard before the join, creating an efficient pipeline. This approach avoids potential out-of-memory (OOM) errors that can occur with a Hash Join when one side of the join is too large to fit in memory.

Related documentation

For more information about Data Definition Language (DDL) statements for Hologres internal tables, see the following topics:

• CREATE TABLE

• CREATE TABLE AS

• CREATE TABLE LIKE

• ALTER TABLE

• DROP TABLE