Using EDB Query Advisor

Suggest edits

EDB Query Advisor defines functions and views.

Functions

query_advisor_index_recommendations(min_filter, min_selectivity)

Performs a global index suggestion.

By default, only predicates filtering at least 1000 rows and 30% of the rows in average are considered. You can use the min_filter and min_selectivity parameters to override the default.

The function generates the one- and two-column index candidates based on the predicates it collects. It replans all related workload queries in the presence of the hypothetical index with respect to each candidate. It recommends the list of indexes that bring the most value to the workload. It also shows the estimated index size and percentage cost reduction in the workload queries. You can decide, based on the size and benefit ratio, which indexes are the most useful for you.

For example:

# running as postgres user
select * from query_advisor_index_recommendations(0,0);
Output
|                             index                             | estimated_size_in_bytes | estimated_pct_cost_reduction |
+---------------------------------------------------------------+-------------------------+------------------------------+
| CREATE INDEX ON nation USING btree (n_name);                  | 8192                    | 14.619857                    |
| CREATE INDEX ON supplier USING btree (s_suppkey,s_nationkey); | 933888                  | 0.63003576                   |
| CREATE INDEX ON partsupp USING btree (ps_suppkey);            | 50159616                | 13.254544                    |
(3 rows)

query_advisor_statistics_recommendations(min_err_estimate_num, min_err_estimate_ratio)

Recommends potentially useful extended statistics by analyzing the statistics collected from the quals of user queries.

By default, min_err_estimate_num and min_err_estimate_ratio are set to 0. You can use the min_err_estimate_num and min_err_estimate_ratio parameters to override the default.

The function generates potential candidates from the multi-column filters of your queries. Then, these candidates are processed by exploring different possible combinations. Currently the focus is on statistics for two columns at a time.

It also shows the weights to each candidate. Weights are based on how many queries would benefit from those extended statistics and what the execution cost of the queries would be.

For example:

# running as postgres user
select * from query_advisor_statistics_recommendations();
Output
|                             statistics                                                 |       weight       |
+----------------------------------------------------------------------------------------+--------------------+
| CREATE STATISTICS part_p_brand_p_container ON p_brand, p_container FROM public.part;   | 4940012.436935346  |
| CREATE STATISTICS part_p_brand_p_type ON p_brand, p_type FROM public.part;             | 306202.2549564565  |
| CREATE STATISTICS part_p_brand_p_size ON p_brand, p_size FROM public.part;             | 2879764.4054564573 |
(3 rows)

query_advisor_qualstat

Returns the counts for every qualifier identified by the expression hash. This hash identifies each expression.

QualifierDescription
useridID of the user who executed the query.
dbidID of the database in which the query was executed.
lrelid, lattnumID of the relation and attribute number of the VAR on the left side, if any.
opnoID of the operator used in the expression.
rrelid, rattnumID of the relation and attribute number of the VAR on the right side, if any.
qualidNormalized identifier of the parent AND expression, if any. This identifier is computed excluding the constants. This qualifier is useful for identifying predicates that are used together.
uniquequalidUnique identifier of the parent AND expression, if any. This identifier is computed including the constants.
qualnodeidNormalized identifier of this simple predicate. This identifier is computed excluding the constants.
uniquequalnodeidUnique identifier of this simple predicate. This identifier is computed including the constants.
occurrencesNumber of times this predicate was invoked, that is, number of related query execution.
execution_countNumber of times this predicate was executed, that is, the number of rows it processed.
nbfilteredNumber of tuples this predicate discarded.
queryidQuery identifier.
eval_typThe evaluation type: f for a predicate evaluated after a scan or i for an index predicate.

For example:

# running as postgres user
select * from query_advisor_qualstats();
Output
| userid | dbid | lrelid | lattnum | opno | rrelid | rattnum | qualid | uniquequalid | qualnodeid | uniquequalnodeid | occurrence | execution_count | nbfiltered | min_err_estimate_ratio | max_err_estimate_ratio | mean_err_estimate_ratio | stddev_err_estimate_ratio | min_err_estimate_num | max_err_estimate_num | mean_err_estimate_num | stddev_err_estimate_num |       queryid        | eval_type |
+--------+------+--------+---------+------+--------+---------+--------+--------------+------------+------------------+------------+-----------------+------------+------------------------+------------------------+-------------------------+---------------------------+----------------------+----------------------+-----------------------+-------------------------+----------------------+-----------+
| 10     | 5    | 16501  | 1       | 96   |        |         |        |              | 262905824  | 262905824        | 1          | 3000000         | 2999997    | 3                      | 3                      | 3                       | 0                         | 2                    | 2                    | 2                     | 0                       | -3469822585968758916 | f         |

query_advisor_workload_queries

Returns all the stored query texts.

query_advisor_qualstats_memory_usage

Returns the percentage usage of the workload and qualifier hash table.

query_advisor_qualstats_reset

Resets the internal counters and forgets about every encountered qualifier and workload entry.

Views

The extension defines the following views on top of the pg_qualstats function.

query_advisor_qualstats

Filters calls to query_advisor_qualstats() by the current database.

query_advisor_qualstats_pretty

Performs the appropriate joins to display a readable, aggregated form for every attribute from the query_advisor_qualstats view.

For example:

# running as ro user

select * from query_advisor_qualstats_pretty;
Output
 left_schema |    left_table    | left_column |   operator   | right_schema | right_table | right_column | occurrences | execution_count | nbfiltered
------------+------------------+-------------+--------------+--------------+-------------+--------------+------------+-----------------+------------
 public     | pgbench_accounts | aid         | pg_catalog.= |              |             |              |          5 |         5000000 |    4999995
 public     | pgbench_tellers  | tid         | pg_catalog.= |              |             |              |         10 |        10000000 |    9999990
 public     | pgbench_branches | bid         | pg_catalog.= |              |             |              |         10 |         2000000 |    1999990
 public     | t1               | id          | pg_catalog.= | public       | t2          | id_t1        |          1 |           10000 |       9999

query_advisor_statistics_recommendations

Skips the weight column from query_advisor_statistics_recommendations.

For example:

# running as postgres user
select * from query_advisor_statistics_recommendations;
Output
|                             statistics                                                 |
+----------------------------------------------------------------------------------------+
| CREATE STATISTICS part_p_brand_p_container ON p_brand, p_container FROM public.part;          |
| CREATE STATISTICS part_p_brand_p_type ON p_brand, p_type FROM public.part;                    |
| CREATE STATISTICS part_p_brand_p_size ON p_brand, p_size FROM public.part;                    |
(3 rows)

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