Hi,
While examining plan caches, I noticed that when a generic plan is invalidated,
the next execution of the prepared statement still results in a
generic plan. This
is of course with the default plan_cache_mode.
This behavior might go unnoticed since plan cache invalidations are
relatively uncommon,
but I’m unsure if this is the intended design. The existing decision
to switch to
a generic plan—based on the cost average of the first five custom
plans—may no longer
be optimal after a relation is modified (e.g., when a new index is added).
Given this, resetting num_custom_plans to 0 after a plan cache invalidation
might be a better approach.
I've attached an example for reference. The fix seems straightforward,
but since generic
plans may already not handle skewed data optimally, I want to see if others have
thoughts on this being something to fix.
--
Sami Imseih
Amazon Web Services (AWS)
DEALLOCATE p1;
DROP TABLE IF EXISTS plan_cache_tab;
CREATE TABLE plan_cache_tab (x int, y int);
INSERT INTO plan_cache_tab SELECT 1, 0 FROM generate_series(1, 50000);
INSERT INTO plan_cache_tab SELECT 2, 0 FROM generate_series(1, 2);
PREPARE P1(int) AS select from plan_cache_tab where x = $1;
EXPLAIN ANALYZE EXECUTE p1(1);
EXPLAIN ANALYZE EXECUTE p1(1);
EXPLAIN ANALYZE EXECUTE p1(1);
EXPLAIN ANALYZE EXECUTE p1(1);
EXPLAIN ANALYZE EXECUTE p1(1);
EXPLAIN ANALYZE EXECUTE p1(1);
EXPLAIN ANALYZE EXECUTE p1(1);
CREATE INDEX ON plan_cache_tab(x);
ANALYZE plan_cache_tab ;
EXPLAIN ANALYZE EXECUTE p1(1);
EXPLAIN ANALYZE EXECUTE p1(1);
DEALLOCATE p1;
PREPARE P1(int) AS select from plan_cache_tab where x = $1;
EXPLAIN ANALYZE EXECUTE p1(1);
DEALLOCATE p1;
test=# DEALLOCATE p1;
DEALLOCATE
test=#
test=# DEALLOCATE p1;
ERROR: prepared statement "p1" does not exist
test=# DROP TABLE IF EXISTS plan_cache_tab;
DROP TABLE
test=# CREATE TABLE plan_cache_tab (x int, y int);
CREATE TABLE
test=# INSERT INTO plan_cache_tab SELECT 1, 0 FROM generate_series(1, 50000);
INSERT 0 50000
test=# INSERT INTO plan_cache_tab SELECT 2, 0 FROM generate_series(1, 2);
INSERT 0 2
test=# PREPARE P1(int) AS select from plan_cache_tab where x = $1;
PREPARE
test=# EXPLAIN ANALYZE EXECUTE p1(1);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Seq Scan on plan_cache_tab (cost=0.00..849.15 rows=251 width=0) (actual
time=0.037..19.157 rows=50000.00 loops=1)
Filter: (x = 1)
Rows Removed by Filter: 2
Buffers: shared hit=222
Planning:
Buffers: shared hit=6
Planning Time: 0.093 ms
Execution Time: 21.764 ms
(8 rows)
test=# EXPLAIN ANALYZE EXECUTE p1(1);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Seq Scan on plan_cache_tab (cost=0.00..849.15 rows=251 width=0) (actual
time=0.014..14.656 rows=50000.00 loops=1)
Filter: (x = 1)
Rows Removed by Filter: 2
Buffers: shared hit=222
Planning Time: 0.041 ms
Execution Time: 17.215 ms
(6 rows)
test=# EXPLAIN ANALYZE EXECUTE p1(1);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Seq Scan on plan_cache_tab (cost=0.00..849.15 rows=251 width=0) (actual
time=0.013..14.668 rows=50000.00 loops=1)
Filter: (x = 1)
Rows Removed by Filter: 2
Buffers: shared hit=222
Planning Time: 0.041 ms
Execution Time: 17.241 ms
(6 rows)
test=# EXPLAIN ANALYZE EXECUTE p1(1);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Seq Scan on plan_cache_tab (cost=0.00..849.15 rows=251 width=0) (actual
time=0.014..14.655 rows=50000.00 loops=1)
Filter: (x = 1)
Rows Removed by Filter: 2
Buffers: shared hit=222
Planning Time: 0.039 ms
Execution Time: 17.211 ms
(6 rows)
test=# EXPLAIN ANALYZE EXECUTE p1(1);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Seq Scan on plan_cache_tab (cost=0.00..849.15 rows=251 width=0) (actual
time=0.014..14.645 rows=50000.00 loops=1)
Filter: (x = 1)
Rows Removed by Filter: 2
Buffers: shared hit=222
Planning Time: 0.038 ms
Execution Time: 17.205 ms
(6 rows)
test=# EXPLAIN ANALYZE EXECUTE p1(1);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Seq Scan on plan_cache_tab (cost=0.00..849.15 rows=251 width=0) (actual
time=0.014..15.119 rows=50000.00 loops=1)
Filter: (x = $1)
Rows Removed by Filter: 2
Buffers: shared hit=222
Planning Time: 0.039 ms
Execution Time: 17.710 ms
(6 rows)
test=# EXPLAIN ANALYZE EXECUTE p1(1);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Seq Scan on plan_cache_tab (cost=0.00..849.15 rows=251 width=0) (actual
time=0.015..15.461 rows=50000.00 loops=1)
Filter: (x = $1)
Rows Removed by Filter: 2
Buffers: shared hit=222
Planning Time: 0.007 ms
Execution Time: 18.083 ms
(6 rows)
test=# CREATE INDEX ON plan_cache_tab(x);
CREATE INDEX
test=# ANALYZE plan_cache_tab ;
ANALYZE
--------------------------------------------------------------------------------
---- a Generic plan is used performing an index scan on the 8th execution,
---- and after a cache invalidation
--------------------------------------------------------------------------------
test=# EXPLAIN ANALYZE EXECUTE p1(1);
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using plan_cache_tab_x_idx on plan_cache_tab
(cost=0.29..643.81 rows=25001 width=0) (actual time=0.059..34.236 rows=50000.00
loops=1)
Index Cond: (x = $1)
Heap Fetches: 50000
Buffers: shared hit=222 read=44
Planning:
Buffers: shared hit=5 read=1
Planning Time: 0.176 ms
Execution Time: 36.819 ms
(8 rows)
test=# EXPLAIN ANALYZE EXECUTE p1(1);
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using plan_cache_tab_x_idx on plan_cache_tab
(cost=0.29..643.81 rows=25001 width=0) (actual time=0.041..33.549 rows=50000.00
loops=1)
Index Cond: (x = $1)
Heap Fetches: 50000
Buffers: shared hit=266
Planning Time: 0.007 ms
Execution Time: 36.152 ms
(6 rows)
-- where actually a sequential scan is better off.
test=# DEALLOCATE p1;
DEALLOCATE
test=# PREPARE P1(int) AS select from plan_cache_tab where x = $1;
PREPARE
test=# EXPLAIN ANALYZE EXECUTE p1(1);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Seq Scan on plan_cache_tab (cost=0.00..847.03 rows=49999 width=0) (actual
time=0.015..14.743 rows=50000.00 loops=1)
Filter: (x = 1)
Rows Removed by Filter: 2
Buffers: shared hit=222
Planning Time: 0.058 ms
Execution Time: 17.321 ms
(6 rows)
test=# DEALLOCATE p1;
DEALLOCATE
test=#
