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=#