Boost Query Performance with Automatic Index Recommendations - PolarDB for PostgreSQL

The automatic index recommendation feature analyzes slow SQL queries and suggests B-tree indexes that can improve their performance. Run an EXPLAIN statement on a slow query, and the feature returns the recommended CREATE INDEX statement along with estimated cost savings—so you can evaluate the improvement before committing to any schema changes.

Prerequisites

Before you begin, ensure that you have:

A PolarDB for PostgreSQL (Compatible with Oracle) cluster running version 2.0, revision 2.0.14.19.0 or later

To check the revision version, run:

show polar_version;

Limitations

Supports B-tree indexes only.
Recommendations are generated per query. Run EXPLAIN on each slow query you want to analyze.
Use the PostgreSQL client to run the examples in this document. The Data Management Service (DMS) client does not display the NOTICE output that contains index recommendations.

How it works

When you run EXPLAIN (without ANALYZE) on a query, polar_advisor inspects the query's execution plan using database statistics and the optimizer cost model. If a B-tree index would reduce the plan cost, the feature outputs:

Field	Description
`advise`	The `CREATE INDEX` statement to run
`old cost`	Plan cost without the index
`new cost`	Estimated plan cost with the index
`saved cost`	Difference between old and new cost
`estimate`	Estimated speed improvement (e.g., 75.5 times faster)

The feature supports the following operators: equality conditions (=), range conditions (<, >), ORDER BY, GROUP BY, and equi-joins.

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. To avoid consuming resources on the primary node, enable it on read-only nodes only:
```
-- Recommended: read-only nodes only.
alter role all set polar_advisor_type to 'index(ro)';
```
To enable it on all nodes instead:
```
alter role all set polar_advisor_type to 'index';
```

Get index recommendations

Use EXPLAIN without the ANALYZE clause on the slow query you want to optimize. Include the /*FORCE_SLAVE*/ hint to route the query to a read-only node.

The following examples use a shared 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;

Output:

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)

Range condition

The feature recommends an index only when the query is selective enough to benefit from one. In the example below, a > 10 returns 9,990 rows (nearly the full table), so no index is recommended. a < 10 returns 9 rows and does get a recommendation.

-- No index recommended: low selectivity (returns most rows).
/*FORCE_SLAVE*/ explain select * from t where a > 10;

-- Index recommended: high selectivity (returns few rows).
/*FORCE_SLAVE*/ explain select * from t where a < 10;

Output for 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)

Multiple conditions (composite index)

When a query filters on multiple columns, the feature may recommend a composite index:

/*FORCE_SLAVE*/ explain select * from t where a = 1 and b = 1;

Output:

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

/*FORCE_SLAVE*/ explain select * from t order by a limit 10;

Output:

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

/*FORCE_SLAVE*/ explain select a, sum(b) from t group by a having a < 10;

Output:

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;

Output:

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)

Apply a recommendation

After reviewing the output, copy the advise statement and run it directly:

CREATE INDEX CONCURRENTLY ON public.t USING btree (a);

Note

If the recommended indexes do not meet your needs or no indexes are recommended, contact Alibaba Cloud support for further analysis.