Index Advisor Components v13

The Index Advisor shared library interacts with the query planner to make indexing recommendations. On Windows, the Advanced Server installer creates the following shared library in the libdir subdirectory of your Advanced Server home directory. For Linux, install the edb-asxx-server-indexadvisor RPM package where xx is the Advanced Server version number.

On Linux:

index_advisor.so

On Windows:

index_advisor.dll

Please note that libraries in the libdir directory can only be loaded by a superuser. A database administrator can allow a non-superuser to use Index Advisor by manually copying the Index Advisor file from the libdir directory into the libdir/plugins directory (under your Advanced Server home directory). Only a trusted non-superuser should be allowed access to the plugin; this is an unsafe practice in a production environment.

The installer also creates the Index Advisor utility program and setup script:

pg_advise_index

pg_advise_index is a utility program that reads a user-supplied input file containing SQL queries and produces a text file containing CREATE INDEX statements that can be used to create the indexes recommended by the Index Advisor. The pg_advise_index program is located in the bin subdirectory of the Advanced Server home directory.

index_advisor_log

Index Advisor logs indexing recommendations in the index_advisor_log table. If Index Advisor does not find the index_advisor_log table in the user's search path, Index Advisor will store any indexing recommendations in a temporary table of the same name. The temporary table exists only for the duration of the current session.

show_index_recommendations()

show_index_recommendations() is a PL/pgSQL function that interprets and displays the recommendations made during a specific Index Advisor session (as identified by its backend process ID).

index_recommendations

Index Advisor creates the index_recommendations view based on information stored in the index_advisor_log table during a query analysis. The view produces output in the same format as the show_index_recommendations() function, but contains Index Advisor recommendations for all stored sessions, while the result set returned by the show_index_recommendations() function are limited to a specified session.