Docs

RDS for PostgreSQL

Extensions

Intelligent index recommendation (index_advisor)

Introduction and applicable scenarios

index_advisor is a practical PostgreSQL extension that automatically analyzes given SQL queries and recommends optimal indexes to improve query performance. This extension generates specific index creation suggestions by internally using hypopg to create hypothetical indexes and evaluate their impact on query plans.

Typical applicable scenarios include:

SQL tuning: use it to quickly retrieve index optimization suggestions for a slow query.

Development phase: assist developers in designing reasonable index strategies for complex queries in new features during development.

Precautions

hypopg dependency: index_advisor's functionality fully depends on the hypopg extension. When installing index_advisor, ensure that hypopg is also installed and enabled.

Index type limitation: currently, index_advisor only supports recommending single-column B-tree indexes. It cannot provide suggestions for multi-column indexes or other types of indexes.

Parameterized query: when analyzing prepared statements containing parameters (such as $1, $2), if the extension cannot automatically infer the data type of the parameter, you may need to explicitly add type conversion for parameters in the query string, such as ... WHERE user_id = $1::integer.

Create and delete extension

Create extension

Execute the CREATE EXTENSION command to install index_advisor. Using the CASCADE clause ensures its dependent hypopg extension is also installed automatically.

Delete extension

Execute the following command to delete the extension.

warning

Uninstalling index_advisor will not automatically uninstall hypopg. If hypopg is no longer needed, it must be uninstalled separately.

Basic usage

The core function of index_advisor is provided through the index_advisor() function. This function accepts a SQL query string as input and returns one or more index creation suggestions.

Function prototype

index_advisor(sql_query TEXT)

Examples

Create an example table

CREATE TABLE book ( id INT PRIMARY KEY, title TEXT NOT NULL, author_id INT NOT NULL);

Recommend an index for a simple query

Analyze a query with a WHERE condition.

SELECT * FROM index_advisor('SELECT id FROM book WHERE title = ''A Tale of Two Cities''');

Recommend indexes for parameterized queries

Analyze a query using placeholders.

SELECT * FROM index_advisor('SELECT id FROM book WHERE author_id = $1');

Recommend indexes for complex JOIN queries

The index_advisor can also handle complex queries with multi-table joins.

-- assume tables author and publisher exist
SELECT * FROM index_advisor('
SELECT b.id, b.title
FROM book b
JOIN author a ON b.author_id = a.id
WHERE a.name = $1
');

Last updated: 2026.05.09 16:35:06