Index Advisor v14

The Index Advisor utility helps determine the columns to index to improve performance in a given workload. Index Advisor considers B-tree (single-column or composite) index types. It doesn't identify other index types, that is, GIN, GiST, and Hash, that might improve performance. Index Advisor is installed with EDB Postgres Advanced Server.

Index Advisor works with EDB Postgres Advanced Server's query planner by creating hypothetical indexes that the query planner uses to calculate execution costs as if such indexes were available. Index Advisor identifies the indexes by analyzing SQL queries supplied in the workload.

You can use Index Advisor to analyze SQL queries in any of these ways:

• Invoke the Index Advisor utility program, supplying a text file containing the SQL queries that you want to analyze. Index Advisor generates a text file with CREATE INDEX statements for the recommended indexes.
• Provide queries at the EDB-PSQL command line that you want Index Advisor to analyze.
• Access Index Advisor through the Postgres Enterprise Manager (PEM) client. When accessed using the PEM client, Index Advisor works with SQL Profiler, providing indexing recommendations on code captured in SQL traces. For more information about using SQL Profiler and Index Advisor with PEM, see Using the Index Advisor in the PEM documentation.

Index Advisor attempts to make indexing recommendations on INSERT, UPDATE, DELETE, and SELECT statements. When invoking Index Advisor, you supply the workload in the form of either:

• If you're providing the command in an SQL file, a set of queries
• If you're specifying the SQL statement at the psql command line, an EXPLAIN statement

Index Advisor displays the query plan and estimated execution cost for the supplied query but doesn't execute the query.

During the analysis, Index Advisor compares the query execution costs with and without hypothetical indexes. If the execution cost using a hypothetical index is less than the execution cost without it:

• Both plans are reported in the EXPLAIN statement output.
• Metrics that quantify the improvement are calculated.
• Index Advisor generates the CREATE INDEX statement needed to create the index.

If no hypothetical index can be found that reduces the execution cost, Index Advisor displays only the original query plan output of the EXPLAIN statement.

An extension supplied with EDB Postgres Advanced Server creates the table in which Index Advisor stores the indexing recommendations generated by the analysis. The extension also creates a function and a view of the table to simplify retrieving and interpreting the results.

If you choose to forgo running the script, Index Advisor logs recommendations in a temporary table that's available only for the current Index Advisor session.