Using Index Advisor

Index Advisor is distributed with EDB Postgres Advanced Server. Index Advisor works with SQL Profiler by examining collected SQL statements and making indexing recommendations for any underlying tables to improve SQL response time. Index Advisor works on all DML (INSERT, UPDATE, DELETE) and SELECT statements that are invoked by a superuser.

Diagnostic output from the Index Advisor includes:

  • Forecasted performance benefits from any recommended indexes

  • The predicted size of any recommended indexes

  • DDL statements you can use to create the recommended indexes

Index Advisor can make indexing recommendations based on trace data captured by SQL Profiler. To open Index Advisor, select one or more queries in the SQL Profiler Trace Data pane and select Index Advisor from the toolbar. For more information about configuring and using Index Advisor, see EDB Postgres Advanced Server.

… note::

Index Advisor can’t analyze statements invoked by a non-superuser. If you attempt to analyze statements invoked by a non-superuser, the server log includes the following error:

ERROR: access to library “index_advisor” is not allowed

Note

  1. It is recommended that you disable the index advisor while using the pg_dump functionality.

  2. Index advisor isn’t supported for EDB Postgres Advanced Server and PostgreSQL versions 16 and later.