F.57. pg_stat_advisor — Tantor SE advisor to create extended statistics#
F.57. pg_stat_advisor — Tantor SE advisor to create extended statistics #
Version: 1.5
F.57.1. Overview #
pg_stat_advisor is a Tantor SE extension designed to analyze query performance and create additional statistics to improve query plan.
F.57.2. Limitations #
For extended statistics to be created, the following conditions must be met:
max_parallel_workers_per_gathermust be set to0(parallel workers disabled).The SQL statement must not be an
UPDATEorDELETE.The execution plan cannot include NestedLoop, MergeJoin, or HashJoin nodes.
The query must be run against a non-temporary table.
The
WHEREclause must filter 2 to 8 columns (inclusive) from a single table.The table has been analyzed and at least one column does have
ndistinct != 1.Extended statistics will not be created if these columns already form a multicolumn index.
Extended statistics must not already exist for these columns on the table.
F.57.3. Installation #
Append pg_stat_advisor to shared_preload_libraries configuration
parameter in your postgresql.conf file then
restart the Tantor SE database to apply the changes.
Note
You cannot use LOAD ‘pg_stat_advisor’; because this extension relies on hooks that must be initialized at server startup.
shared_preload_libraries = 'pg_stat_advisor';
F.57.4. Usage #
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.
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;
F.57.5. Examples #
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)