All Products
Search
Document Center

AnalyticDB for PostgreSQL:Manage indexes

Last Updated:Jan 25, 2024

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

Usage notes

  • AnalyticDB for PostgreSQL in Serverless mode allows you to create only B-tree indexes.

  • If you create indexes for an AnalyticDB for PostgreSQL instance in Serverless mode, the scaling performance of the instance is affected. The amount of time required for scaling is proportional to the data volume of indexes.

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 frequently queried in conjunction with other indexed columns, bitmap indexes perform better than other index types.

  • BRIN index (available only for AnalyticDB for PostgreSQL V6.0)

  • GIN index (available only for AnalyticDB for PostgreSQL V6.0)

  • GiST index (available only for AnalyticDB for PostgreSQL V6.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 for a column that has a high selectivity.

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

  • Create a bitmap index for a column that has a low selectivity.

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

  • 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 similar performance as B-tree indexes but occupy less space.

Methods to select appropriate columns to create indexes:

  • Create an index for a column that is frequently used for joins with other tables.

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

  • Create an index for 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 for a frequently updated column.

    If you create an index for a column that is frequently updated, 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 for 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, load the data, and then recreate indexes for 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 for a table. Examples:

  • B-tree index

    Create a B-tree index for the gender column of the employee table.

    CREATE INDEX gender_idx ON employee (gender);
  • Bitmap index

    Create a bitmap index for the title column of the films table.

    CREATE INDEX title_bmp_idx ON films USING bitmap (title);
  • BRIN index

    Create a BRIN index for 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

    Create a GIN index for the l_comment column of the lineitem table. Only AnalyticDB for PostgreSQL V6.0 supports GIN indexes.

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

    Create a GIN index for the intarray column of the arrayt table. Only AnalyticDB for PostgreSQL V6.0 supports GIN indexes.

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

    Create a GiST index for the c_comment column of the customer table. Only AnalyticDB for PostgreSQL V6.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 for a table. Examples:

  • Recreate the my_index index.

    REINDEX INDEX my_index;
  • Recreate all indexes for 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, load the data, and then recreate indexes for the table. This is faster than updating the indexes.

Collect indexed data

You can execute the VACUUM statement to collect indexed data. For example, 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.