This topic describes the index types of AnalyticDB for PostgreSQL and their related operations.

Index types

AnalyticDB for PostgreSQL supports the following index types:

  • B-tree index (default index type)
  • Bitmap index
    Note

    Bitmap indexes enable AnalyticDB for PostgreSQL to store bitmaps that each contain the values of a key. Bitmap indexes serve the same purpose as a conventional index but occupy less storage space. In scenarios where indexed columns consist of 100 to 100,000 distinct values and are often queried in conjunction with other indexed columns, bitmap indexes perform better than other index types.

  • BRIN index (available only for AnalyticDB for PostgreSQL 6.0 in minor version 20210324 or later)
  • GIN index (available only for AnalyticDB for PostgreSQL 6.0)
  • GiST index(available only for AnalyticDB for PostgreSQL 6.0)
Note AnalyticDB for PostgreSQL does not support hash indexes.

Principles for indexing

Scenarios in which to create indexes:

  • Small datasets are returned from a query.

    Indexes help increase the performance of queries on single data records or small datasets. Such queries include online transaction processing (OLTP) queries.

  • Compressed tables are used.

    On a compressed append-optimized (AO) table, indexes help increase the performance of queries because only the involved rows are decompressed.

Methods to select index types:

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

    For example, in a table that has 1,000 rows, if 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 on a column that has a low selectivity.

    Bitmap indexes perform better than other index types in scenarios where indexed columns consist of 100 to 100,000 distinct values.

  • Create a BRIN index if a large amount of data is sequentially distributed and if filter conditions such as <, <=, =, >=, and > are used to filter data.

    When large datasets are involved, BRIN indexes can provide the same performance but occupy less space compared with B-tree indexes.

Methods to select appropriate columns to create indexes:

  • 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.

    The most suitable column is the one that is frequently referenced in WHERE clauses.

  • 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.

Best practices for using indexes:

  • Do not create redundant indexes.

    If an index is created on more than one column, indexes that have the same leading column are redundant.

  • Delete indexes before you batch load data.

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

  • Test and compare the performance of queries that use and do not use indexes.

    Create indexes only when the performance of queries on the indexed columns improves.

  • Execute the ANALYZE statement on a table after you create an index.

Create an index

You can execute the CREATE INDEX statement to create an index on a table. Examples:

  • B-tree index

    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);
  • Bitmap index

    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);
  • BRIN index

    Execute the following statement to create a BRIN index on the c_custkey column of the customer table:

    CREATE INDEX c_custkey_brin_idx ON customer USING brin(c_custkey) with(pages_per_range=2);
  • GIN index

    Execute the following statement to create a GIN index on the l_comment column of the lineitem table. Only AnalyticDB for PostgreSQL 6.0 supports GIN indexes.

    CREATE INDEX lineitem_idx ON lineitem USING gin(to_tsvector('english', l_comment));
  • GIN index

    Execute the following statement to create a GIN index on the intarray column of the arrayt table. Only AnalyticDB for PostgreSQL 6.0 supports GIN indexes.

    CREATE INDEX arrayt_idx ON arrayt USING gin(intarray);
  • GiST index

    Execute the following statement to create a GiST index on the c_comment column of the customer table. Only AnalyticDB for PostgreSQL 6.0 supports GiST indexes.

    CREATE INDEX customer_idx ON customer USING gist(to_tsvector('english', c_comment));

Recreate an index

You can execute the REINDEX INDEX statement to recreate an index on a table. Examples:

  • Execute the following statement to recreate the my_index index:

    REINDEX INDEX my_index;
  • Execute the following statement to recreate all indexes on the my_table table:

    REINDEX TABLE my_table;

Delete an index

You can execute the DROP INDEX statement to delete an index from a table. For example, execute the following statement to delete the title_idx index:

DROP INDEX title_idx;
Note If you want to load a large amount of data to a table, we recommend that you delete all existing indexes on the data, load the data, and then recreate indexes on the table. This is faster than updating the indexes.

Collect indexed data

You can execute the VACUUM statement to collect indexed data. For example, you can execute the following statement to collect indexed data from the customer table:

VACUUM customer;
Note Indexed data collection is available only for BRIN indexes.

References

For more information about indexes, see the Pivotal Greenplum documentation.