Manage indexes

Index types

AnalyticDB for PostgreSQL supports the following index types:

• B-tree index

• 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 (applicable only to heap tables)

• GIN Index

• GiST index

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 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 on 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 on 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 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 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 with and without indexes used.

  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. Full text search is supported.

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

  • Create a GIN index on the intarray column of the arrayt table.

    CREATE INDEX arrayt_idx ON arrayt USING gin(intarray);

• GiST index

  Create a GiST index for the c_comment column of the customer table. Full text search is supported.

  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;

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;

References

For more information about indexes, see Pivotal Greenplum documentation.