Hologres:Accelerate point queries with global secondary index

Supported data types

Index columns support only the following data types: TEXT, INTEGER, BIGINT, and VARCHAR.

Table requirements

Index constraints

Source table restrictions

• Columns that are part of a global secondary index cannot be deleted or modified in the source table.

• Table Group and resharding operations cannot be performed on a source table that has a global secondary index.

Storage

By default, global secondary indexes support only Standard storage (hot storage).

Syntax

CREATE GLOBAL INDEX [ IF NOT EXISTS ] index_name
  ON [schema_name.]table_name (index_column_name [, ...])
  [ INCLUDE (include_column_name [, ...]) ];

Parameters

Usage notes

• The CREATE GLOBAL INDEX statement blocks until the index is fully built and visible. For large tables, this may take a significant amount of time.

• Index building affects write performance because it involves writing multiple copies of data. The impact increases with the data volume of the source table and the number of columns in the index.

• The index is always created in the same schema as the source table. You cannot specify a schema in the CREATE GLOBAL INDEX statement.

• A query uses the global secondary index only when all columns in the query are covered by the index (either as index columns or INCLUDE columns). If the query references any column not in the index, the optimizer falls back to scanning the source table.

Syntax

DROP INDEX [schema_name.]index_name;

Parameters

Step 1: Create the source table

The orders table has nine columns. O_ORDERKEY is the primary key.

-- Create the orders table with row-column hybrid storage
CREATE TABLE ORDERS
(
    O_ORDERKEY      BIGINT        NOT NULL PRIMARY KEY,
    O_CUSTKEY       INT           NOT NULL,
    O_ORDERSTATUS   CHAR(1)       NOT NULL,
    O_TOTALPRICE    DECIMAL(15,2) NOT NULL,
    O_ORDERDATE     DATE          NOT NULL,
    O_ORDERPRIORITY TEXT          NOT NULL,
    O_CLERK         TEXT          NOT NULL,
    O_SHIPPRIORITY  INT           NOT NULL,
    O_COMMENT       TEXT          NOT NULL
) WITH (
  orientation='row,column',                                        -- Row-column hybrid store
  segment_key='O_ORDERDATE',                                       -- Segment by order date
  distribution_key='O_ORDERKEY',                                   -- Distribute by primary key
  bitmap_columns='O_ORDERSTATUS,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY',
  dictionary_encoding_columns='o_comment:off,o_orderpriority,o_clerk'
);

COMMENT ON TABLE ORDERS IS 'Primary order table that records basic order information and status';
COMMENT ON COLUMN ORDERS.O_ORDERKEY IS 'Order number (primary key)';
COMMENT ON COLUMN ORDERS.O_CUSTKEY IS 'Customer ID (foreign key associated with the CUSTOMER table)';
COMMENT ON COLUMN ORDERS.O_ORDERSTATUS IS 'Order status (''F'' = Finished, ''O'' = Open, ''P'' = Processing)';
COMMENT ON COLUMN ORDERS.O_TOTALPRICE IS 'Total order amount';
COMMENT ON COLUMN ORDERS.O_ORDERDATE IS 'Order creation date';
COMMENT ON COLUMN ORDERS.O_ORDERPRIORITY IS 'Order priority (''1-URGENT'', ''2-HIGH'', etc.)';
COMMENT ON COLUMN ORDERS.O_CLERK IS 'Employee ID of the person who processed the order';
COMMENT ON COLUMN ORDERS.O_SHIPPRIORITY IS 'Shipping priority (higher value means higher priority)';
COMMENT ON COLUMN ORDERS.O_COMMENT IS 'Order comment information';

Step 2: Check the execution plan without an index

Run a point query that filters by order priority:

-- Query all urgent orders
SELECT
  O_ORDERKEY,
  O_CUSTKEY,
  O_ORDERSTATUS,
  O_TOTALPRICE,
  O_ORDERDATE,
  O_ORDERPRIORITY,
  O_CLERK,
  O_SHIPPRIORITY,
  O_COMMENT
FROM ORDERS
WHERE O_ORDERPRIORITY = '1-URGENT';

Check the execution plan:

EXPLAIN
SELECT
  O_ORDERKEY,
  O_CUSTKEY,
  O_ORDERSTATUS,
  O_TOTALPRICE,
  O_ORDERDATE,
  O_ORDERPRIORITY,
  O_CLERK,
  O_SHIPPRIORITY,
  O_COMMENT
FROM ORDERS
WHERE O_ORDERPRIORITY = '1-URGENT';

The output shows the query uses a Bitmap Filter on the Clustering_index. This provides only limited performance improvement:

QUERY PLAN
Gather  (cost=0.00..1.00 rows=1 width=53)
  ->  Local Gather  (cost=0.00..1.00 rows=1 width=53)
        ->  Index Scan using Clustering_index on orders  (cost=0.00..1.00 rows=1 width=53)
              Bitmap Filter: (o_orderpriority = '1-URGENT'::text)
Query Queue: init_warehouse.default_queue
Optimizer: HQO version 4.0.0

Step 3: Create a global secondary index

Create an index on O_ORDERPRIORITY and include all other columns needed by the query:

-- Create a global secondary index on order priority
-- INCLUDE all columns that the query needs
CREATE GLOBAL INDEX idx_orders ON orders(O_ORDERPRIORITY)
INCLUDE (
  O_CUSTKEY,
  O_ORDERSTATUS,
  O_TOTALPRICE,
  O_ORDERDATE,
  O_CLERK,
  O_SHIPPRIORITY,
  O_COMMENT
);

Step 4: Verify the index is used

Run the same EXPLAIN query. The execution plan now shows the optimizer scans the global secondary index idx_orders instead of the source table, and applies shard pruning:

QUERY PLAN
Local Gather  (cost=0.00..1.76 rows=3035601 width=99)
  ->  Index Scan using Clustering_index on idx_orders  (cost=0.00..1.54 rows=3035601 width=99)
        Shard Prune: Eagerly
        Shards selected: 1 out of 20
        Cluster Filter: (o_orderpriority = '1-URGENT'::text)
Query Queue: init_warehouse.default_queue
Optimizer: HQO version 4.0.0

Key differences from the plan without the index:

Step 5: Optimize further with Fixed Plan

Enable Fixed Plan to increase QPS for point queries:

SET hg_experimental_enable_fixed_dispatcher_for_scan = true;

After you enable this setting, the point query uses a Fixed Plan, which reduces query planning overhead and further improves throughput.