Index

Supports index-related functionality on time-series tables, allowing users to use indexes to improve query performance as needed. Supported index types on time-series tables include B-tree and Gin. Partial indexes are not supported, but B-tree indexes can be unique. Indexes on time-series tables are created as LOCAL indexes by default, and GLOBAL indexes are not supported. When data is deleted from a time-series table, the corresponding indexes on the partition are also deleted.

Create an Index

Index creation syntax is the same as that for a partition table. However, GLOBAL index creation is not supported.

CREATE [ UNIQUE ] INDEX [ [schema_name.]index_name ] ON table_name [ USING method ]
    ( {{ column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS LAST ] }[, ...] )
    [ LOCAL [ ( { PARTITION index_partition_name | SUBPARTITION index_subpartition_name [ TABLESPACE index_partition_tablespace ] } [, ...] ) ] ]
    [ INCLUDE ( column_name [, ...] )]
    [ WITH ( { storage_parameter = value } [, ...] ) ]
    [ TABLESPACE tablespace_name ];

Modify an Index

• Rename a table index.

  ALTER INDEX [ IF EXISTS ] index_name RENAME TO new_name;

• Modify the storage parameters of a table index.

  ALTER INDEX [ IF EXISTS ] index_name SET ( {storage_parameter = value} [, ... ] );

• Set a table index or index partition unavailable

  ALTER INDEX [ IF EXISTS ] index_name [ MODIFY PARTITION index_partition_name ] UNUSABLE;

• Reset a certain storage parameter of one or more index methods of an index as the default. Similar to SET, REINDEX may need to be used for updating an index.

  ALTER INDEX [ IF EXISTS ] index_name RESET ( { storage_parameter } [, …] );

• Recreate an index in a table or index partition.

  ALTER INDEX [ IF EXISTS ] index_name REBUILD [ PARTITION index_partition_name ];

• Rename an index partition.

  ALTER INDEX [ IF EXISTS ] index_name RENAME PARTITION index_partition_name TO new_index_partition_name;

• Modify the tablespace to which an index partition belongs.

  ALTER INDEX [ IF EXISTS ] index_name MOVE PARTITION index_partition_name TABLESPACE new_tablespace;

Delete an Index

DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] index_name [, ...] [ CASCADE | RESTRICT ];

Rebuild an Index

REINDEX  { INDEX|  [INTERNAL] TABLE} name PARTITION partition_name [ FORCE  ];