EDB Query Advisor

EDB Query Advisor is a Postgres extension that provides index recommendations by keeping statistics on predicates found in WHERE statements, JOIN clauses, and workload queries. It analyzes the cost-benefit to actual workload queries by replanning them with the hypothetical index. Hypothetical indexes are created based on the predicates collected from the workload queries.

This extension is useful if you want to analyze the most useful indexes that can benefit the workload queries without creating all possible indexes.

The predicate collection works by looking for known patterns in queries. These patterns include:

• Binary OpExpr — At least one side is a column from a table. Whenever possible, the predicate is swapped so that CONST OP VAR expressions are turned into VAR COMMUTED_OP CONST. AND and OR expression members are counted as separate entries, for example, WHERE column1 = 2, WHERE column1 = column2, WHERE 3 = column3.

• ScalarArrayOpExpr — The left side is a VAR, and the right side is an array constant. Those are counted one time per element in the array. For example, WHERE column1 IN (2, 3) is counted as two occurrences for the (column1, '=') operator pair.

• BooleanTest — The expression is a simple Boolean column reference, for example, WHERE column1 IS TRUE. Clauses like WHERE columns1, WHERE NOT column1 aren't currently processed.

The extension saves the first query text, as is, for each distinct queryid executed in the workload table.

The gathered data isn't saved when the Postgres server is restarted.

See Using EDB Query Advisor for a description of the query_advisor_index_recommendations function you use to generate the index recommendation.

Limitations

• Only single and two-column indexes are considered.
• Statistics aren't collected for utility commands.
• The number of predicates and workload entries are static. A change requires a restart.

• On this page
• Limitations