EDB Tutorial: How To Get the Most Out of EDB Query Advisor

Enterprise-class databases require DBAs who are responsible for maximizing query performance.  Therefore, when it comes to large scale deployment of databases, minimizing the work of DBA is important.  One of the main task of performance improvement is to choosing the right set of indexes. To do this effectively, the DBA needs to analyze a broad portion of the database workload and subsequently analyze the query plans that appear regularly in the workload and represent a significant portion of the “work” in the workload. Once this has been completed the DBA can evaluate what indexes could be helpful.  In some cases this analysis could be wrong and in some cases adding the index could result in worse performance.

Automatic index selection tools are helpful in identifying a set of indexes which can help the overall workload and without actually spending time and space on creating actual indexes.

EDB query_advisor is a new automatic index advisor tool.  When the statistics collection feature is enabled, this tool continuously collects real-time workload statistics and recommends indexes based on those statistics.  This tool also ensures that the indexes are actually useful to the workload by experimenting with hypothetical indexes based on the actual workload, not suggesting indexes that might not be beneficial to the workload.  Additionally, an estimate of each recommended index's size and percentage cost reduction will be provided.  Therefore, the user can decide whether to create that index based on that input.

Unlike index advising tools for some databases, the EDB query advisor has the ability to recommend multi-column indexes as well as different types of indexes based on its analysis of your workload.

In addition, the query advisor is designed to have minimal overhead so you can use it on a production system without being overly concerned about performance.

How it works

• When queries are executed the query advisor collects the predicate and workload information from the queries.  For this purpose we are using modified version of pg_qualstats code.  We store them in a hash.
• Whenever a user asks for index recommendations, we process those stats and based on that we generate index candidates.  Further we process those candidates and generate hypothetical indexes with respect to those candidates using HypoPG tool.
• Internally at each stage the index selection process will select the indexes which can provide maximum benefit with least overhead to the workload we have collected.
• Currently we are only computing index size overhead but in future we are planning to compute the overhead that will occur on indexes due to creation of those particular indexes.

How to use query advisor 

Configuring EDB Query Advisor

• In the postgresql.conf file, add query_advisor to the shared_preload_librariesparameter:

shared_preload_libraries=’query_advisor’

• Restart Postgres.
• Create the EDB Query Advisor extension in your database:
   

 CREATE EXTENSION query_advisor;

query advisor configuration parameters

The following custom GUCs control the EDB Query Advisor extension behavior.  If you modify these parameters, then reload Postgres to apply the changes:

• query_advisor.enabled — Specifies whether to enable query_advisor. It takes a Boolean value. The default is true.
• query_advisor.sample_rate — Sets the fraction of queries to sample. It takes a double value. For example, 0.1 indicates to sample only 1 out of 10 queries. The default is -1, which specifies automatic and results in a value of 1 / max_connections.

 If you modify these parameters, then restart Postgres to apply the changes:

• query_advisor.max_qual_entries — Sets the maximum number of predicates tracked.
• query_advisor.max_workload_entries — Sets the maximum number of workload queries tracked.
• query_advisor.max_workload_query_size — Sets the maximum size of the workload query.

Generating index recommendations

• In order to generate the index recommendation call query_advisor_index_recommendation() function
• This will provide exact sql statement to created the recommended indexes
• Along with the index statements this will also provide estimated size and estimated % cost reduction for the workload.
• So based on the size and % cost reduction DBA can actually decide whether creating this index is really worth it for or not.

Example of using query_advisor

Create query advisor extension and a test table and insert 1M record in it

CREATE EXTENSION query_advisor ;
SET query_advisor.sample_rate =1;

CREATE TABLE test(a int, b varchar, c date);
INSERT INTO test SELECT i, 'test' || i, now() FROM generate_series(1, 1000000) AS i;
ANALYZE test;

Execute a query and see the timing of execution

\timing on
SELECT * FROM test WHERE c < '2023-01-30';
Time: 34.534 ms

EXPLAIN SELECT * FROM test WHERE c < '2023-01-30';
                              QUERY PLAN                               
------------------------------------------------------------------------
 Gather  (cost=1000.00..12577.43 rows=1 width=18)
   Workers Planned: 2
   ->  Parallel Seq Scan on test  (cost=0.00..11577.33 rows=1 width=18)
         Filter: (c < '2023-01-30'::date)
(4 rows)

Get index recommendations

SELECT * FROM query_advisor_index_recommendations(0,0);
       index                                                                 | estimated_size_in_bytes | estimated_pct_cost_reduction 
-------------------------------------------------------------------+---------------------------------+----------------------------------------
 CREATE INDEX ON public.test USING btree (c);  |                       26124288  |                                99.97712
(1 row)

Create recommended index(s)

CREATE INDEX ON public.test USING btree (c);

Execute the query again and check time (We can see the query runs very fast and the new index is getting used in the plan)

SELECT * FROM test WHERE c < '2023-01-30';
Time: 1.394 ms

EXPLAIN SELECT * FROM test WHERE c < '2023-01-30';
                               QUERY PLAN                               
------------------------------------------------------------------------
 Index Scan using test_c_idx on test  (cost=0.42..4.44 rows=1 width=18)
   Index Cond: (c < '2023-01-30'::date)
(2 rows)

Future blogs:

1. Multicolumn index recommendations via the query advisor.
2. Getting recommendations for Brin indexes via the query advisor.

Notes:

• Query advisor is now available with BigAnimal.  You can start a free trial of Big Animal here: https://www.enterprisedb.com/products/biganimal-cloud-postgresql
• Refer query advisor document for more information https://www.enterprisedb.com/docs/pg_extensions/query_advisor/