Version: 1.0.0

Github

pg_stat_advisor is a Tantor BE extension designed to analyze query performance and create additional statistics to improve query plan.

For extended statistics to be created, the following conditions must be met: 1. max_parallel_workers_per_gather must be set to 0 (parallel workers disabled). 2. The SQL statement must not be an UPDATE or DELETE. 3. The execution plan cannot include NestedLoop, MergeJoin, or HashJoin nodes. 4. The query must be run against a non-temporary table. 5. The WHERE clause must filter 2 to 8 columns (inclusive) from a single table. 6. The table has been analyzed and at least one column does have ndistinct != 1. 7. Extended statistics will not be created if these columns already form a multicolumn index. 8. Extended statistics must not already exist for these columns on the table.

Tantor BE 17 or higher

In order to build and install the extension do “make”, and then “sudo make install”.

make
sudo make install

If this extension is compiled with PGXS-infrastructure, so the pg_config tool must be available from your PATH environment variable. And then do “USE_PGXS=1 make, and then”sudo USE_PGXS=1 make install".

USE_PGXS=1 make
sudo USE_PGXS=1 make install

Append pg_stat_advisor to shared_preload_libraries configuration parameter in your postgresql.conf file then restart the Tantor BE database to apply the changes.

shared_preload_libraries = 'pg_stat_advisor';

The pg_stat_advisor.version parameter displays the current version of the extension; it is read-only and intended for informational purposes.

SHOW pg_stat_advisor.version;

There is the pg_stat_advisor.suggest_statistics_threshold GUC that can be used to set a suggest_statistics_threshold. It is the the ratio of the planned rows compared to the total tuples produced. If parameter is set by 0, the printing switches off.

For example:

SET pg_stat_advisor.suggest_statistics_threshold = 0.1;

The pg_stat_advisor.min_duration GUC sets the minimum query duration (in milliseconds) required for the extension to activate; setting it to -1 completely disables the extension.

SET pg_stat_advisor.min_duration = 100;

The pg_stat_advisor.ring_buffer_capacity parameter sets the size (in bytes) of the ring buffer used to store data about extended statistics that will be created; this buffer operates in a circular manner, overwriting old entries when full. It can only be set in postgresql.conf.

pg_stat_advisor.ring_buffer_capacity = 1024

To set the maximum number of workers that will generate and collect extended statistics, adjust the pg_stat_advisor.deferred_jobs_max parameter in postgresql.conf.

SET pg_stat_advisor.pg_stat_advisor.deferred_jobs_max = 1024;

To collect statistics immediately after creating extended statistics, enable the pg_stat_advisor.enable_analyze parameter. To defer collection and let autovacuum or manual ANALYZE handle it, disable this parameter.

SET pg_stat_advisor.enable_analyze = on;

To limit the number of table columns used when creating extended statistics, adjust the pg_stat_advisor.ext_stats_max_columns parameter.

SET pg_stat_advisor.ext_stats_max_columns = 8;

SET pg_stat_advisor.suggest_statistics_threshold = 0.1;

CREATE TABLE t (i INT, j INT);
INSERT INTO t SELECT i/10, i/100 FROM generate_series(1, 1000000) i;
ANALYZE t;
EXPLAIN ANALYZE SELECT * FROM t WHERE i = 100 AND j = 10;
                                                   QUERY PLAN

----------------------------------------------------------------------------------------------
------------------
 Gather  (cost=1000.00..11675.10 rows=1 width=8) (actual time=0.526..61.564 rows=10 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on t  (cost=0.00..10675.00 rows=1 width=8) (actual time=35.369..54.44
7 rows=3 loops=3)
         Filter: ((i = 100) AND (j = 10))
         Rows Removed by Filter: 333330
 Planning Time: 0.148 ms
 Execution Time: 61.589 ms
(8 rows)


EXPLAIN ANALYZE SELECT * FROM t WHERE i = 100 AND j = 10;
                                                   QUERY PLAN
----------------------------------------------------------------------------------------------
------------------
 Gather  (cost=1000.00..11675.10 rows=10 width=8) (actual time=0.400..59.292 rows=10 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on t  (cost=0.00..10675.00 rows=1 width=8) (actual time=35.614..54.29
1 rows=3 loops=3)
         Filter: ((i = 100) AND (j = 10))
         Rows Removed by Filter: 333330
 Planning Time: 0.081 ms
 Execution Time: 59.413 ms
(8 rows)

Ilia Evdokimov, Eduard Stepanov