Automatic index recommendation

Background information

Database performance optimization is a key task in database management. Indexes are vital for improving SQL query performance. A good index helps the database quickly find specific rows in a table and retrieve sorted data, which greatly shortens query time.

However, not all indexes improve performance. A suitable index can greatly speed up queries, but an unsuitable index might not be helpful and can even slow down the database because of maintenance overhead. Indexes also use storage space, and too many useless indexes waste storage resources. Deciding when and on which columns to create an index requires technical skill and experience. This decision often requires a deep analysis of query execution plans and data distribution.

To help solve this challenge and optimize database performance, PolarDB for PostgreSQL and offer the automatic index recommendation feature. This feature simplifies the process of creating indexes, which makes developing and maintaining database indexes easier. You can use the EXPLAIN command to analyze a slow SQL statement. The system then suggests a suitable index and estimates the expected performance improvement.

The main benefits of the automatic index recommendation feature are:

• Precise recommendations: The feature uses automatically collected statistics and the optimizer's cost model to recommend the correct index for a specific SQL query. This avoids the performance issues and trial-and-error costs associated with adding indexes blindly.

• Efficiency improvements: The feature reduces the time required to manually analyze slow queries. This helps you quickly find and fix performance bottlenecks, which shortens the time to locate and solve problems.

• Easy to use: A simple EXPLAIN command provides index recommendations. The feature also provides the SQL command to create the index and predicts the performance gain. This streamlines your workflow.

Usage guide

Configure automatic index recommendation

1. Add polar_advisor to session_preload_libraries to load polar_advisor for all users.

   -- The session_preload_libraries parameter is empty by default. This command adds polar_advisor.
   alter role all set session_preload_libraries to 'polar_advisor';

2. Enable index recommendation on specific nodes for all users. We recommend that you enable this feature only on read-only (RO) nodes to avoid consuming compute resources on the read-write (RW) node.

   -- Enable index recommendation on RO nodes. [Recommended]
   alter role all set polar_advisor_type to 'index(ro)';

   -- Enable index recommendation on all nodes.
   alter role all set polar_advisor_type to 'index';

Use automatic index recommendation

When you generate an execution plan for a slow SQL statement using the EXPLAIN command without the ANALYZE statement, the automatic index recommendation feature is triggered. If a recommended index is found, the system automatically outputs the following information:

• advise: The command to create the recommended index.

• old cost: The cost of the original plan.

• new cost: The estimated cost of the plan after the recommended index is created.

• saved cost: The cost saved by the new plan compared to the old plan.

• estimate: The estimated performance improvement.

The automatic index recommendation feature can identify the following types of operators. Currently, only B-tree indexes can be recommended. The following examples demonstrate how this feature works.

Create a table and insert data.

create table t( a int,b int);
insert into t select i,i from generate_series(1,10000)i;
analyze t;

• Recommend a single-column index based on an 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)

• Recommend a single-column index based on a comparison condition.

   /*FORCE_SLAVE*/ explain select * from t where a > 10; -- The index does not filter much data, 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)
  
   /*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)
  

• Recommend a compound index based on multiple equality or comparison conditions.

  /*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)

• Recommend a single-column index based on the ORDER BY operator.

   /*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)

• Recommend a single-column index based on the GROUP BY operator.

  /*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)

• Recommend a single-column index based on an equi-join operation.

  /*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)