9.2 Using the Index Advisor

Get Postgres Tips and Tricks

Subscribe to get advanced Postgres how-tos.

9 SQL Profiling and Analysis : 9.2 Using the Index Advisor

9.2 Using the Index Advisor

Index Advisor is distributed with Advanced Server 9.0 and above. Index Advisor works with SQL Profiler by examining collected SQL statements and making indexing recommendations for any underlying tables to improve SQL response time. The 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

Before using Index Advisor, you must:

1.

Modify the postgresql.conf file on each Advanced Server host, adding the index_advisor library to the shared_preload_libraries parameter.

2.

Install the Index Advisor contrib module. To install the module, use the psql client or PEM Query Tool to connect to the database, and invoke the following command:

\\i <complete_path>/share/contrib/index_advisor.sql

3.

Restart the server for your changes to take effect.

Index Advisor can make indexing recommendations based on trace data captured by SQL Profiler. Simply highlight one or more queries in the SQL Profiler Trace Data pane, and click the Index Advisor toolbar button (or select Index Advisor from the View menu). For detailed usage information about Index Advisor, please see the EDB Postgres Advanced Server Guide

Please note: Index Advisor cannot analyze statements invoked by a non-superuser. If you attempt to analyze statements invoked by a non-superuser, the server log will include the following error:

ERROR: access to library "index_advisor" is not allowed

For more information about configuring and using Index Advisor, please see the EDB Postgres Advanced Server Guide, available from EnterpriseDB at:

https://www.enterprisedb.com/resources/product-documentation

9 SQL Profiling and Analysis : 9.2 Using the Index Advisor