Boost PostgreSQL Query Performance with Automatic Index Recommendations - PolarDB

Slow queries caused by missing indexes are common in production databases, but identifying the right index to create requires deep knowledge of query execution plans and data distribution. Automatic index recommendation eliminates that guesswork: run EXPLAIN on any slow query and the system outputs a ready-to-run CREATE INDEX statement along with the estimated performance gain — no manual plan analysis needed.

Applicability

Supported on PolarDB for PostgreSQL clusters running PostgreSQL 14 with minor engine version 2.0.14.10.19.0 or later.

PolarDB for PostgreSQL distributed clusters do not support this feature.

To check your minor engine version, run SHOW polardb_version; in psql or view it in the PolarDB console. If your version does not meet the requirement, upgrade the minor engine version.

How it works

When you run EXPLAIN on a query — without the ANALYZE option — the feature uses automatically collected statistics and the optimizer's cost model to evaluate potential indexes and outputs a recommendation before the query plan is displayed.

If a suitable index is found, the output includes:

Field	Description
`advise`	The `CREATE INDEX CONCURRENTLY` statement to run
`old cost`	Optimizer cost of the current query plan
`new cost`	Estimated cost after the index is created
`saved cost`	Difference between old and new cost
`estimate`	Estimated speedup (e.g., `75.5 times faster`)

If no beneficial index is found, the system returns only the standard EXPLAIN output.

The advisor recommends only B-tree indexes. ANALYZE must not be added to the EXPLAIN command — doing so executes the query and bypasses the advisor.

Prerequisites

Before you begin, ensure that you have:

A PolarDB for PostgreSQL cluster running PostgreSQL 14, minor engine version 2.0.14.10.19.0 or later
A psql client (the DMS client does not display NOTICE output from this feature)

Configure automatic index recommendation

Load polar_advisor for all users by adding it to session_preload_libraries.

-- session_preload_libraries is empty by default.
ALTER ROLE ALL SET session_preload_libraries TO 'polar_advisor';

Enable index recommendation on read-only (RO) nodes to avoid consuming compute resources on the read-write (RW) node.
Use the /*FORCE_SLAVE*/ hint in your queries to route them to an RO node when testing.
```
-- Enable on RO nodes only (recommended)
ALTER ROLE ALL SET polar_advisor_type TO 'index(ro)';

-- Enable on all nodes
ALTER ROLE ALL SET polar_advisor_type TO 'index';
```

Get index recommendations

Run EXPLAIN on a slow query. Do not add the ANALYZE option — ANALYZE executes the query and bypasses the advisor.

/*FORCE_SLAVE*/ EXPLAIN SELECT * FROM t WHERE a = 1;

The advisor recognizes the following SQL patterns:

Equality conditions (WHERE col = value)
Comparison conditions (WHERE col < value, WHERE col > value) — only when selective
Multiple equality or comparison conditions — composite index
ORDER BY
GROUP BY
Equi-join (t1.col = t2.col)

The following sections show how the feature handles each pattern. All examples use the same test table:

CREATE TABLE t (a INT, b INT);
INSERT INTO t SELECT i, i FROM generate_series(1, 10000) i;
ANALYZE t;

Equality condition

/*FORCE_SLAVE*/ EXPLAIN SELECT * FROM t WHERE a = 1;

INFO:  IND ADV: advise "CREATE INDEX CONCURRENTLY ON public.t USING btree (a)"
NOTICE:  IND ADV:   old cost 0.00..170.00
NOTICE:  IND ADV:   new cost 0.04..2.25
NOTICE:  IND ADV: saved cost -0.04..167.75
NOTICE:  IND ADV: estimate 75.5 times faster
                    QUERY PLAN
---------------------------------------------------
 Seq Scan on t  (cost=0.00..170.00 rows=1 width=8)
   Filter: (a = 1)
(2 rows)

Comparison condition

The advisor recommends an index only when the condition is selective enough. A highly selective condition (a < 10) triggers a recommendation; a low-selectivity condition (a > 10, which matches 99.9% of rows) does not.

/*FORCE_SLAVE*/ EXPLAIN SELECT * FROM t WHERE a < 10;

INFO:  IND ADV: advise "CREATE INDEX CONCURRENTLY ON public.t USING btree (a)"
NOTICE:  IND ADV:   old cost 0.00..170.00
NOTICE:  IND ADV:   new cost 0.04..2.39
NOTICE:  IND ADV: saved cost -0.04..167.61
NOTICE:  IND ADV: estimate 71.1 times faster
                    QUERY PLAN
---------------------------------------------------
 Seq Scan on t  (cost=0.00..170.00 rows=9 width=8)
   Filter: (a < 10)
(2 rows)

/*FORCE_SLAVE*/ EXPLAIN SELECT * FROM t WHERE a > 10;
-- The condition matches nearly all rows, so no index is recommended.
                      QUERY PLAN
------------------------------------------------------
 Seq Scan on t  (cost=0.00..170.00 rows=9990 width=8)
   Filter: (a > 10)
(2 rows)

Multiple conditions (composite index)

When a query filters on multiple columns, the advisor recommends a composite index.

/*FORCE_SLAVE*/ EXPLAIN SELECT * FROM t WHERE a = 1 AND b = 1;

INFO:  IND ADV: advise "CREATE INDEX CONCURRENTLY ON public.t USING btree (b, a)"
NOTICE:  IND ADV:   old cost 0.00..195.00
NOTICE:  IND ADV:   new cost 0.04..1.16
NOTICE:  IND ADV: saved cost -0.04..193.84
NOTICE:  IND ADV: estimate 168.8 times faster
                    QUERY PLAN
---------------------------------------------------
 Seq Scan on t  (cost=0.00..195.00 rows=1 width=8)
   Filter: ((a = 1) AND (b = 1))
(2 rows)

ORDER BY with LIMIT

/*FORCE_SLAVE*/ EXPLAIN SELECT * FROM t ORDER BY a LIMIT 10;

INFO:  IND ADV: advise "CREATE INDEX CONCURRENTLY ON public.t USING btree (a)"
NOTICE:  IND ADV:   old cost 361.10..361.12
NOTICE:  IND ADV:   new cost 0.04..0.26
NOTICE:  IND ADV: saved cost 361.06..360.86
NOTICE:  IND ADV: estimate 1366.8 times faster
                                QUERY PLAN
-------------------------------------------------------------------
 Limit  (cost=361.10..361.12 rows=10 width=8)
   ->  Sort  (cost=361.10..386.10 rows=10000 width=8)
         Sort Key: a
         ->  Seq Scan on t  (cost=0.00..145.00 rows=10000 width=8)
(4 rows)

GROUP BY with HAVING

/*FORCE_SLAVE*/ EXPLAIN SELECT a, sum(b) FROM t GROUP BY a HAVING a < 10;

INFO:  IND ADV: advise "CREATE INDEX CONCURRENTLY ON public.t USING btree (a)"
NOTICE:  IND ADV:   old cost 170.14..170.30
NOTICE:  IND ADV:   new cost 0.04..2.53
NOTICE:  IND ADV: saved cost 170.11..167.77
NOTICE:  IND ADV: estimate 67.4 times faster
                              QUERY PLAN
---------------------------------------------------------------
 GroupAggregate  (cost=170.14..170.30 rows=9 width=12)
   Group Key: a
   ->  Sort  (cost=170.14..170.17 rows=9 width=8)
         Sort Key: a
         ->  Seq Scan on t  (cost=0.00..170.00 rows=9 width=8)
               Filter: (a < 10)
(6 rows)

Equi-join

/*FORCE_SLAVE*/ EXPLAIN SELECT * FROM t t1, t t2 WHERE t1.a = t2.a LIMIT 10;

INFO:  IND ADV: advise "CREATE INDEX CONCURRENTLY ON public.t USING btree (a)"
NOTICE:  IND ADV:   old cost 270.00..270.28
NOTICE:  IND ADV:   new cost 0.07..0.70
NOTICE:  IND ADV: saved cost 269.93..269.58
NOTICE:  IND ADV: estimate 384.3 times faster
                                     QUERY PLAN
----------------------------------------------------------------------------
 Limit  (cost=270.00..270.28 rows=10 width=16)
   ->  Hash Join  (cost=270.00..552.50 rows=10000 width=16)
         Hash Cond: (t1.a = t2.a)
         ->  Seq Scan on t t1  (cost=0.00..145.00 rows=10000 width=8)
         ->  Hash  (cost=145.00..145.00 rows=10000 width=8)
               ->  Seq Scan on t t2  (cost=0.00..145.00 rows=10000 width=8)
(6 rows)

Limitations

Limitation	Details
B-tree indexes only	The advisor recommends only B-tree indexes. GIN, GiST, BRIN, and hash index types are not supported.
Distributed clusters not supported	PolarDB for PostgreSQL distributed clusters do not support this feature.
psql client required	The DMS client does not display `NOTICE` messages, so index recommendations will not appear. Use psql to see the full output.
`EXPLAIN` only	Adding `ANALYZE` to `EXPLAIN` executes the query and bypasses the advisor. Always use `EXPLAIN` without `ANALYZE`.

If a recommended index does not perform as expected, or no index is recommended for a query you expect to benefit from indexing, contact support for troubleshooting.