Hello PostgreSQL Hackers,
I'm thrilled to share with you a new PostgreSQL extension I've developed, called 'pg_stat_advisor'. The genesis of this extension traces back to a conversation in this: https://www.postgresql.org/message-id/e2512fd5-77a4-825b-e456-c0586e37f293%40enterprisedb.com
The 'pg_stat_advisor' extension is architected to optimize query plan. It operates by suggesting when to create extended statistics, particularly in queries where current selectivity estimates fall short. This is achieved through the GUC parameter 'pg_stat_advisor.suggest_statistics_threshold', which assesses the ratio of total tuples compared to the planned rows. This feature is instrumental in identifying scenarios where the planner's estimates could be optimized.
A key strength of 'pg_stat_advisor' lies in its ability to recommend extended statistics, significantly bolstering query planning.
You can install the extension by
LOAD 'pg_stat_advisor';
SET pg_stat_advisor.suggest_statistics_threshold = 1.0;
Example:
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;
NOTICE: pg_stat_advisor suggestion: CREATE STATISTICS t_i_j ON i, j FROM t
QUERY PLAN
----------------------------------------------------------------------------------------------
------------------
Gather (cost=1000.00..11675.10 rows=1 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)
After EXPLAIN ANALYZE command you can see the message of suggestion creating statistics with name 't_i_j' on 'i', 'j' columns from 't' table.
I look forward to your thoughts, feedback, and any suggestions you might have.
Best regards.
Ilia Evdokimov,
Tantor Labs LLC.