EDB Query Advisor

Suggest edits

EDB Query Advisor is a Postgres extension that includes the Index Advisor and the Statistics Advisor.

Index Advisor

The Index Advisor 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.

Statistics Advisor

Statistics play a crucial role in determining the quality of query execution plans chosen by the database query optimizer. Sometimes there are dependencies between columns, and unless we create combined statistics using extended statistics, the optimizer assumes these columns are independent. This assumption often leads to incorrect cardinality estimates, which in turn, results in a poor execution plan, negatively impacting query performance.

To address this, we monitor queries where multiple-column filters are applied and check for any selectivity estimation errors. If such errors are detected, we treat those multi-column filters as potential candidates for extended statistics.

Whenever extended statistics recommendations are generated, we process these candidates by exploring various combinations of columns, especially when more than two columns are involved in the filters. Since estimation errors alone don’t definitively indicate which columns are dependent, we try all possible combinations. Currently, to limit the candidate pool, we focus on statistics for no more than two columns at a time.

We also assign weights to each candidate, prioritising them based on how many queries would benefit from those extended statistics and what is the execution cost of the queries are.

See Using EDB Query Advisor for a description of the query_advisor_statistics_recommendations function you can use to generate the statistics recommendation.

Limitations

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

Could this page be better? Report a problem or suggest an addition!