Index types

AnalyticDB for PostgreSQL supports B-tree and bitmap indexes, but does not support hash indexes. Only AnalyticDB for PostgreSQL V6.0 supports GIN indexes and GiST indexes.
Note The default index type is B-tree. Bitmap indexes enable AnalyticDB for PostgreSQL to store bitmaps that each contain the values of a key. Bitmap indexes provide the same functions as a conventional index while occupying less storage space. Bitmap indexes perform best for columns that have 100 to 100,000 distinct values and when indexed columns are often queried in conjunction with other indexed columns.

Create an index

Execute a CREATE INDEX statement to create a B-tree index.

Examples:

Execute the following statement to create a B-tree index on the gender column of the employee table:
CREATE INDEX gender_idx ON employee (gender);
Execute the following statement to create a bitmap index on the title column of the films table:
CREATE INDEX title_bmp_idx ON films USING bitmap (title);
Execute the following statement to create a GIN index on the l_comment column of the lineitem table. Note that only AnalyticDB for PostgreSQL V6.0 supports GIN indexes.
CREATE INDEX lineitem_idx ON lineitem USING gin(to_tsvector('english', l_comment));
Execute the following statement to create a GIN index on the intarray column of the arrayt table. Note that only AnalyticDB for PostgreSQL V6.0 supports GIN indexes.
CREATE INDEX arrayt_idx ON arrayt USING gin(intarray);
Execute the following statement to create a GiST index on the c_comment column of the customer table. Note that only AnalyticDB for PostgreSQL V6.0 supports GiST indexes.
CREATE INDEX customer_idx ON customer USING gist(to_tsvector('english', c_comment));

Rebuild an index

Execute a REINDEX INDEX statement to rebuild an index.

Examples:

Execute the following statement to rebuild the my_index index:
REINDEX INDEX my_index;
Execute the following statement to rebuild all indexes on the my_table table:
REINDEX TABLE my_table;

Delete an index from a table

Execute a DROP INDEX statement to delete an index.

Example:

DROP INDEX title_idx;
Note If you want to load a large volume of data to a table, we recommend that you first delete all indexes on the data and then load the data to quickly rebuild the indexes on the table.

Principles for indexing

  • Create an index based on query loads.

    A query load-based index helps increase the performance of queries for single data records or small data sets. Such queries include online transaction processing (OLTP) queries.

  • Create an index on a compressed table.

    On a compressed append-optimized table, an index helps increase the performance of queries for single rows because only the involved rows are decompressed.

  • Do not create an index on a frequently updated column.

    If you create an index on a frequently updated column, the amount of data that needs to be read and written for column updates increases.

  • Create a B-tree index that has a high selectivity.

    For example, if a table has 1,000 rows and you create an index on a column that has 800 distinct values, the selectivity of the index is 0.8. The selectivity of an index created on a column that has the same value in all rows is always 1.0.

  • Create a bitmap index that has a low selectivity. Bitmap indexes perform best for columns that have 100 to 100,000 distinct values.
  • Create an index on a column that is frequently used for joins with other tables.

    For example, create an index on a column used as the foreign key. This enables the query optimizer to use more join methods and therefore increases join performance.

  • Create an index on a column that is frequently referenced in predicates.

    For example, create an index on a column that is frequently referenced in WHERE clauses.

  • Do not create redundant indexes.

    For example, if an index is created on more than one column, indexes with the same leading column are redundant.

  • Delete indexes before you load data.

    If you want to load a large volume of data into a table, we recommend that you delete all indexes on the data, load the data, and then rebuild the indexes on the table. This is faster than updating indexes.

  • Test and compare the performance of queries with and without indexes used.

    Do not create indexes unless the performance of queries for indexed columns increases.

  • Execute the ANALYZE statement after you create or update an index.

References

For more information, visit CREATE INDEX in Pivotal Greenplum documentation.