Hologres:Global Secondary Index

Prerequisites

Your Hologres instance must be V4.0 or later. If your instance is an earlier version, see Instance upgrade.

Limitations

• Tables with a global secondary index do not support writing data using Fixed FE or Fixed Copy.

• Global secondary index columns support only the TEXT, INTEGER, BIGINT, and VARCHAR data types.

• You cannot modify a global secondary index.

• A column cannot be used as both an index column and an include column.

• The source table must have a primary key.

• The time_to_live_in_seconds parameter cannot be set on the source table.

• The total number of index columns and include columns in a global secondary index cannot exceed 512.

• Global secondary indexes can be created only on internal tables. Physical partitioned tables and logical partitioned tables are not supported.

• You cannot delete or modify a column in the source table if it is part of a global secondary index.

• You cannot modify the Table Group or perform Resharding on a source table that has a global secondary index.

• By default, global secondary indexes use only standard storage.

• A global secondary index uses the same storage format as its source table: row-oriented storage, column-oriented storage, or row-column hybrid storage.

  • If the source table uses row-oriented storage, its global secondary index also uses row-oriented storage by default.

  • If the source table uses column-oriented storage, its global secondary index also uses column-oriented storage by default.

  • If the source table uses row-column hybrid storage, its global secondary index also uses row-column hybrid storage by default.

Create a global secondary index

• Syntax

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

• Parameters

  Parameter	Required	Description
  index_name	Yes	The name of the global secondary index.
  schema_name	No	The schema name of the source table. If you do not specify this parameter, the default schema is used.
  table_name	Yes	The name of the source table.
  index_column_name	Yes	The columns to index. For best results, use the columns that you filter on in point queries against non-primary keys.
  include_column_name	No	The columns to include in the global secondary index.

• Usage notes

  • After you submit the SQL statement, the system starts to build the index. The CREATE GLOBAL INDEX operation completes only after the index is built and becomes visible.

  • Building an index writes extra data, which affects write performance. This impact grows with the amount of data in the source table and the number of columns in the index.

  • A global secondary index is always created in the same schema as its source table. You cannot specify a different schema for the index.

  • A query can use a global secondary index only if all referenced columns are covered by the index, as either index or include columns.

Drop a global secondary index

• Syntax

  DROP INDEX [schema_name.]index_name

• Parameters

  Parameter	Required	Description
  schema_name	No	The schema name of the global secondary index. If you do not specify this parameter, the default schema is used.
  index_name	Yes	The name of the global secondary index.

View global secondary indexes

• View all global secondary indexes

  SELECT 
      n.nspname AS table_namespace,
      t.relname AS table_name,
      i.relname AS index_name
  FROM 
      pg_class t
  JOIN 
      pg_index ix ON t.oid = ix.indrelid
  JOIN 
      pg_class i ON i.oid = ix.indexrelid
  JOIN 
      pg_am am ON am.oid = i.relam
  JOIN
      pg_namespace n ON n.oid = t.relnamespace 
  WHERE 
      t.relkind = 'r'  -- Query only regular tables.
      AND am.amname = 'globalindex'

• View global secondary index storage size

  Where global_index_name is the name of the global secondary index.

  SELECT pg_relation_size('schema_name.global_index_name');

• View included columns

  SELECT pg_catalog.pg_get_indexdef('global_index_name'::regclass, 0, true);

Examples

Suppose your order application frequently queries data by order priority. The following example uses the orders table:

The SQL statement for creating the sample orders table:

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',
  segment_key='O_ORDERDATE',
  distribution_key='O_ORDERKEY',
  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 'Main order table that stores basic order information and status.';
COMMENT ON COLUMN ORDERS.O_ORDERKEY IS 'The order ID (primary key).';
COMMENT ON COLUMN ORDERS.O_CUSTKEY IS 'The customer ID (a foreign key that references the CUSTOMER table).';
COMMENT ON COLUMN ORDERS.O_ORDERSTATUS IS 'The order status (''F'' = Finished, ''O'' = Open, ''P'' = Processing).';
COMMENT ON COLUMN ORDERS.O_TOTALPRICE IS 'The total price of the order.';
COMMENT ON COLUMN ORDERS.O_ORDERDATE IS 'The date when the order was created.';
COMMENT ON COLUMN ORDERS.O_ORDERPRIORITY IS 'The order priority (''1-URGENT'', ''2-HIGH'', etc.).';
COMMENT ON COLUMN ORDERS.O_CLERK IS 'The ID of the employee who processed the order.';
COMMENT ON COLUMN ORDERS.O_SHIPPRIORITY IS 'The shipping priority. A larger value indicates a higher priority.';
COMMENT ON COLUMN ORDERS.O_COMMENT IS 'Comments about the order.';

• If you frequently run a query like the following to fetch orders of a specific priority:

  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'

  You can use EXPLAIN to check the execution plan of an SQL statement:

  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'

  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

• You can perform more efficient queries by adding an index to the O_ORDERPRIORITY column.

  The returned plan shows the query used a Bitmap Index, which provides limited performance improvement. To achieve higher QPS, add a global secondary index on the O_ORDERPRIORITY column:

  CREATE GLOBAL INDEX idx_orders ON orders(O_ORDERPRIORITY)
  INCLUDE (
    O_CUSTKEY,
    O_ORDERSTATUS,
    O_TOTALPRICE,
    O_ORDERDATE,
    O_CLERK,
    O_SHIPPRIORITY,
    O_COMMENT
    );

  After adding the index, run the EXPLAIN statement again to check the execution plan:

  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

  The plan now shows that Index Scan using Clustering_index on targets the global secondary index idx_orders, and shard pruning is applied. This effectively improves QPS.

• Use a Fixed Plan to further improve QPS.

  SET hg_experimental_enable_fixed_dispatcher_for_scan = true;

  The execution plan now shows a FixedSelectNode on the index, which confirms that the query is optimized with a Fixed Plan for maximum performance.

  SET hg_experimental_enable_fixed_dispatcher_for_scan = true;
  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'
  QUERY PLAN
  FixedSelectNode on idx_orders  (cost=0.00..0.00 rows=0 width=0)
    Qual: o_orderpriority =>  ranges: {['1-URGENT'::text,'1-URGENT'::text]}
  Optimizer: HQO version 4.0.0