Hologres:global secondary index

Prerequisites

Your Hologres instance must be V4.0 or later. If your instance is earlier than V4.0, see Upgrade an instance.

Limits

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

• You cannot modify a global secondary index.

• Index columns and INCLUDE columns cannot contain duplicate columns.

• The primary table must have a primary key.

• The total number of index columns and INCLUDE columns in a global secondary index cannot exceed 256.

• Only standard internal tables support global secondary indexes. Physical and logical partitioned tables do not support this feature.

• You cannot delete or modify columns in the primary table if they are part of a global secondary index.

• You cannot modify the table group or reshard a primary table that has a global secondary index.

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

• The storage mode (row store, column store, or row-column hybrid store) of a global secondary index is the same as its primary table. The details are as follows:

  • If the primary table uses row store, its global secondary index also uses row store by default.

  • If the primary table uses column store, its global secondary index also uses column store by default.

  • If the primary table uses a row-column hybrid store, its global secondary index also uses a row-column hybrid store 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[, ...]) ]

• Parameter descriptions

  Parameter	Required	Description
  index_name	Yes	The name of the global secondary index.
  schema_name	No	The schema name of the primary table. If you do not specify this parameter, the default schema name is used.
  table_name	Yes	The name of the primary table.
  index_column_name	Yes	The index column of the global secondary index. Set this to a filter column used for point queries on non-primary keys.
  include_column_name	No	The columns to include in the global secondary index.

• Usage notes

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

  • Because index building is equivalent to writing multiple copies of data, it affects write performance. The larger the data volume of the primary table, the greater the impact on write performance. In addition, the more columns the index contains, the greater the impact.

  • When you create a global secondary index, you cannot specify a schema. The global secondary index is created in the same schema as the primary table.

  • For a query to use a global secondary index, the index must completely cover all columns involved in the query. This means the columns must be either index columns or included columns.

Delete a global secondary index

• Syntax

  DROP INDEX [schema_name.]index_name

• Parameter descriptions

  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 global secondary indexes in the current database

  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 standard tables
      AND am.amname = 'globalindex'

• View the storage used by a global secondary index

  In the statement, global_index_name is the name of the global secondary index.

  SELECT pg_relation_size('schema_name.global_index_name');

• View the columns included in a global secondary index

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

Examples

Assume that an order application needs to frequently query data based on a specific order priority. The sample orders table is shown below:

The following SQL statement creates 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 '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 price';
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 'ID of the employee who processed the order';
COMMENT ON COLUMN ORDERS.O_SHIPPRIORITY IS 'Shipping priority (a larger value indicates a higher priority)';
COMMENT ON COLUMN ORDERS.O_COMMENT IS 'Order comments';

• The following SQL statement is used to frequently query data for a specific order 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 the 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'

  The following result is returned.

  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

• Improve query efficiency by adding an index to the O_ORDERPRIORITY column.

  The execution plan returned above shows that the query uses a bitmap index. A bitmap index provides limited improvement to query performance. If you need higher queries per second (QPS), you can add an index to the O_ORDERPRIORITY column of the orders table.

  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 you add the index, you can 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 the object of Index Scan using Clustering_index on is the global secondary index idx_orders. Shard pruning is also used. This significantly improves QPS.

• Use a fixed plan to further improve QPS.

  SET hg_experimental_enable_fixed_dispatcher_for_scan = true;

  You can view the execution plan to see the point query optimization that uses a fixed plan.