AnalyticDB for PostgreSQL supports B-tree, bitmap, BRIN, GiST, and GIN indexes. Hash indexes are not supported. B-tree is the default index type and suits most common scenarios.
Before creating an index, verify that it provides better performance than a full table scan for your specific query workload. Start by running queries without indexes.
In Serverless mode, only B-tree indexes can be created.
When indexes help — and when they don't
AnalyticDB for PostgreSQL is optimized for rapid sequential scans across distributed compute nodes. Data is distributed evenly across all nodes, and each node scans only its own share of the data — so when a query touches a large portion of the dataset, a sequential scan is already efficient. For BI report queries that return large result sets, indexes provide no additional benefit.
Indexes are most effective in OLTP (online transaction processing) scenarios where a query returns a single row or a small dataset. They also add overhead: more storage, write amplification, and index maintenance during data updates.
Unique index constraints
Before creating unique indexes, note the following constraints:
Index key columns must be identical to or a superset of the distribution key columns.
Append-optimized (AO) tables do not support unique indexes.
Unique indexes apply only within a single partition — not across partitions in a partitioned table.
Choose the right index type
Use the table below to match your column characteristics to the right index type, then read the corresponding section for details.
| Scenario | Recommended index type |
|---|---|
| High-cardinality column (many distinct values) | B-tree |
| Low-cardinality column (1,000–100,000 distinct values) | Bitmap |
| Large table with range queries returning < 50% of rows | Sparse (BRIN or AOCO metadata) |
| Query on a computed expression | Expression index |
B-tree index
B-tree indexes work best on columns with high selectivity. Index selectivity is the ratio of distinct values to total rows. For example, a table with 1,000 rows and 800 distinct values in a column has a selectivity of 0.8 — a good candidate for a B-tree index. Columns with fully unique values are ideal for B-tree indexes.
Bitmap index
Bitmap indexes are designed for columns with 1,000 to 100,000 distinct values. When sort keys are also used to aggregate data, bitmap indexes can significantly accelerate queries. They are best suited for data warehousing workloads where data is queried but not frequently updated.
When not to use bitmap indexes:
Avoid bitmap indexes on columns with more than 100,000 distinct values.
Do not use bitmap indexes on frequently updated tables or in OLTP applications with high-concurrency write workloads.
Sparse index (BRIN and AOCO metadata)
Sparse indexes reduce I/O by skipping blocks of data that don't match the query predicate. Use a sparse index when your query retrieves less than 50% of rows using <, <=, =, >=, or > operators. Sparse index types include BRIN indexes and the metadata feature of append-optimized column-oriented (AOCO) tables.
Sparse indexes also work well on compressed AO and AOCO tables: less data loaded means less data to decompress, reducing CPU usage.
Expression index
An index key can be a function or scalar expression computed from one or more columns, not just a raw column value. This lets you index computed results for fast lookups.
For example, to speed up the following query:
SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';Create an expression index:
CREATE INDEX people_names ON people ((first_name || ' ' || last_name));Index guidelines
| Guideline | Details |
|---|---|
| Join columns | Create indexes on columns frequently used in JOIN operations. |
| Filter columns | Create indexes on columns frequently referenced in WHERE clauses. |
| Compressed tables | Indexes improve query efficiency on compressed AO and AOCO tables. |
| Avoid duplicate indexes | Multiple indexes of the same type on the same leading column do not improve query performance and add write overhead. |
| Avoid indexes on frequently updated columns | Each update to a column triggers an index update, which degrades write performance. |
| Use sort keys or CLUSTER for row-oriented tables | Physical data ordering improves index efficiency, especially for BRIN and sparse indexes. Use compound or interleaved sort keys to optimize data distribution. For row-oriented tables with B-tree indexes, run CLUSTER to implement physical sorting. |
| Drop indexes before bulk loads | Drop indexes before loading large amounts of data, then recreate them after the load completes. This is faster than incrementally updating indexes during the load. |