Use index_adviser to improve query performance for PostgreSQL - ApsaraDB RDS

The index_adviser extension analyzes your queries and recommends indexes to improve performance for specific workloads. It identifies which columns to index and generates ready-to-run CREATE INDEX statements.

Limitation: index_adviser recommends single-column and composite B-tree indexes only. It does not recommend GIN, GiST, or Hash indexes.

Prerequisites

Before you begin, ensure that:

The RDS instance runs minor engine version 20230830 or later

Important

If your instance runs a minor engine version earlier than 20230830 and you have already used this extension, you can continue to use it. To create or re-create the extension, upgrade the minor engine version to the latest version first. For background on plugin security requirements, see Restrictions on creating plugins.

Components

Creating the extension automatically creates three components:

Component	Type	Description
`index_advisory`	Table	Records indexing suggestions generated during query analysis
`show_index_advisory()`	PL/pgSQL function	Returns formatted `CREATE INDEX` statements for a specific session, identified by its backend process ID
`select_index_advisory`	View	Shows indexing suggestions across all sessions; same format as `show_index_advisory()` output

Set up the extension

Create the extension.

postgres=# create extension index_adviser;
CREATE EXTENSION

Load the extension for the current session.
```
postgres=# LOAD 'index_adviser';
LOAD
```
Note
The LOAD statement applies to the current session only. To load the extension automatically for all sessions, add it to the shared_preload_libraries parameter and restart the instance. This may affect instance performance.
```
shared_preload_libraries='index_adviser'
```

Get index recommendations

Analyze a single query without running it

Prefix any query with EXPLAIN to get index recommendations without executing the query. The extension embeds recommendations directly in the query plan output.

The following example creates a test table and runs three EXPLAIN queries:

CREATE TABLE t( a INT, b INT );
INSERT INTO t SELECT s, 99999 - s FROM generate_series(0,99999) AS s;
ANALYZE t;

postgres=# EXPLAIN SELECT * FROM t WHERE a < 10000;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..1693.00 rows=9983 width=8)
   Filter: (a < 10000)
 Result  (cost=0.00..0.00 rows=0 width=0)
   One-Time Filter: '** plan (using Index Adviser) **'::text
   ->  Index Scan using "<1>t_a_idx" on t  (cost=0.42..256.52 rows=9983 width=8)
         Index Cond: (a < 10000)
(6 rows)

postgres=# EXPLAIN SELECT * FROM t WHERE a = 100;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..1693.00 rows=1 width=8)
   Filter: (a = 100)
 Result  (cost=0.00..0.00 rows=0 width=0)
   One-Time Filter: '** plan (using Index Adviser) **'::text
   ->  Index Scan using "<1>t_a_idx" on t  (cost=0.42..2.64 rows=1 width=8)
         Index Cond: (a = 100)
(6 rows)

postgres=# EXPLAIN SELECT * FROM t WHERE b = 10000;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..1693.00 rows=1 width=8)
   Filter: (b = 10000)
 Result  (cost=0.00..0.00 rows=0 width=0)
   One-Time Filter: '** plan (using Index Adviser) **'::text
   ->  Index Scan using "<1>t_b_idx" on t  (cost=0.42..2.64 rows=1 width=8)
         Index Cond: (b = 10000)
(6 rows)

The One-Time Filter: ' plan (using Index Adviser) ' line signals that the extension has recorded recommendations. Query the index_advisory table to see them.

Analyze queries during execution

Without the EXPLAIN prefix, the extension analyzes queries as they run and records recommendations automatically.

Note

Do not use index_adviser in read-only transactions.

Query the index_advisory table

After running queries (with or without EXPLAIN), query the index_advisory table to see the raw recommendations:

postgres=# SELECT * FROM index_advisory;
 reloid | relname | attrs | benefit | original_cost | new_cost | index_size | backend_pid |            timestamp
--------+---------+-------+---------+---------------+----------+------------+-------------+----------------------------------
  16438 | t       | {1}   | 1337.43 |          1693 |  355.575 |       2624 |       79370 | 18-JUN-21 08:55:51.492388 +00:00
  16438 | t       | {1}   | 1684.56 |          1693 |    8.435 |       2624 |       79370 | 18-JUN-21 08:59:00.319336 +00:00
  16438 | t       | {2}   | 1684.56 |          1693 |    8.435 |       2624 |       79370 | 18-JUN-21 08:59:07.814453 +00:00
(3 rows)

Field descriptions:

Field	Type	Description
`reloid`	oid	Object identifier (OID) of the table
`relname`	name	Name of the table
`attrs`	integer[]	Column number for the recommended index
`benefit`	real	Estimated query cost reduction from the index
`original_cost`	real	The average amount of time required to execute the SQL statement before using the index to accelerate the query
`new_cost`	real	The average amount of time required to execute the SQL statement after using the index to accelerate the query
`index_size`	integer	Estimated index size in disk pages
`backend_pid`	integer	Backend process ID of the session that generated this recommendation
`timestamp`	timestamp	When the recommendation was generated

Get session-level recommendations

The show_index_advisory() function and select_index_advisory view aggregate per-query recommendations into session-level summaries and output ready-to-run CREATE INDEX statements.

Use show_index_advisory()

Call the function with a backend process ID to get recommendations for that session. Pass null to get recommendations for the current session:

postgres=# SELECT show_index_advisory(null);
                                                             show_index_advisory
----------------------------------------------------------------------------------------------------------------------------------------------------
 create index idx_t_a on public.t(a);/* size: 2624 KB, benefit: 3021.99, gain: 1.15167301457103, original_cost: 1693, new_cost: 182.005006313324 */
 create index idx_t_b on public.t(b);/* size: 2624 KB, benefit: 1684.56, gain: 0.641983590474943, original_cost: 1693, new_cost: 8.4350004196167 */
(2 rows)

Each row contains a CREATE INDEX statement followed by a comment with these metrics:

Metric	Description
`size`	Estimated index size in KB — `MAX(index_size)` across all queries that benefit from this index
`benefit`	Total cost reduction across all queries that benefit from this index — `SUM(benefit)` per query
`gain`	Benefit-to-size ratio: `SUM(benefit) / MAX(index_size)`. A higher value means the index delivers more benefit relative to the disk space it consumes
`original_cost`	The average amount of time required to execute the SQL statement before using the index to accelerate the query
`new_cost`	The average amount of time required to execute the SQL statement after using the index to accelerate the query. If multiple indexes are recommended for one query, this reflects the cost after all recommended indexes are applied

To get recommendations for a specific session, pass its backend process ID from the backend_pid column of the index_advisory table:

SELECT show_index_advisory(79370);

Use select_index_advisory

The select_index_advisory view shows recommendations for all sessions in the index_advisory table, grouped by backend process ID:

postgres=# SELECT * FROM select_index_advisory;
 backend_pid |                                                             show_index_advisory
-------------+----------------------------------------------------------------------------------------------------------------------------------------------------
       79370 | create index t_a_idx on public.t(a);/* size: 2624 KB, benefit: 3021.99, gain: 1.15167301457103, original_cost: 1693, new_cost: 182.005006313324 */
       79370 | create index t_b_idx on public.t(b);/* size: 2624 KB, benefit: 1684.56, gain: 0.641983590474943, original_cost: 1693, new_cost: 8.4350004196167 */
(2 rows)

Use the gain value to prioritize which indexes to create first. A higher gain means the index provides greater query cost reduction relative to the disk space it consumes.