When you are using prepared statements in PostgreSQL you might get a custom or a generic plan. Custom plans come with overhead of re-planning while generic plans avoid re-planning of the statement. A soon as you get a generic plan that plan will be used for the lifetime of your session and there are situations when you do not want to see this as a generic plan might be more expensive than a custom plan. Starting with PostgreSQL 12 (which currently is in beta) you have more control over this.
Let use the same simple test case as in the previous post about custom and generic plans:
postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------
PostgreSQL 12devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.2.1 20180905 (Red Hat 8.2.1-3), 64-bit
(1 row)
postgres=# create table demo ( a int, b text );
CREATE TABLE
postgres=# insert into demo select i, 'aaa' from generate_series (1,100) i;
INSERT 0 100
postgres=# insert into demo select i, 'bbb' from generate_series (101,200) i;
INSERT 0 100
postgres=# insert into demo select i, 'ccc' from generate_series (201,300) i;
INSERT 0 100
postgres=# analyze demo;
ANALYZE
postgres=#
When we prepare and then execute the blow statement 6 times we will see a generic plan:
postgres=# prepare my_stmt as select * from demo where b = $1;
PREPARE
postgres=# explain (analyze) execute my_stmt ( 'aaa' );
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on demo (cost=0.00..5.75 rows=100 width=8) (actual time=0.027..0.107 rows=100 loops=1)
Filter: (b = 'aaa'::text)
Rows Removed by Filter: 200
Planning Time: 0.431 ms
Execution Time: 0.198 ms
(5 rows)
postgres=# explain (analyze) execute my_stmt ( 'aaa' );
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on demo (cost=0.00..5.75 rows=100 width=8) (actual time=0.032..0.113 rows=100 loops=1)
Filter: (b = 'aaa'::text)
Rows Removed by Filter: 200
Planning Time: 0.196 ms
Execution Time: 0.155 ms
(5 rows)
postgres=# explain (analyze) execute my_stmt ( 'aaa' );
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on demo (cost=0.00..5.75 rows=100 width=8) (actual time=0.032..0.113 rows=100 loops=1)
Filter: (b = 'aaa'::text)
Rows Removed by Filter: 200
Planning Time: 0.168 ms
Execution Time: 0.154 ms
(5 rows)
postgres=# explain (analyze) execute my_stmt ( 'aaa' );
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on demo (cost=0.00..5.75 rows=100 width=8) (actual time=0.041..0.135 rows=100 loops=1)
Filter: (b = 'aaa'::text)
Rows Removed by Filter: 200
Planning Time: 0.238 ms
Execution Time: 0.193 ms
(5 rows)
postgres=# explain (analyze) execute my_stmt ( 'aaa' );
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on demo (cost=0.00..5.75 rows=100 width=8) (actual time=0.031..0.112 rows=100 loops=1)
Filter: (b = 'aaa'::text)
Rows Removed by Filter: 200
Planning Time: 0.169 ms
Execution Time: 0.154 ms
(5 rows)
postgres=# explain (analyze) execute my_stmt ( 'aaa' );
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on demo (cost=0.00..5.75 rows=100 width=8) (actual time=0.033..0.120 rows=100 loops=1)
Filter: (b = $1)
Rows Removed by Filter: 200
Planning Time: 0.163 ms
Execution Time: 0.163 ms
(5 rows)
PostgreSQL 12 will come with a new parameter to control that:
postgres=# show plan_cache_mode;
plan_cache_mode
-----------------
auto
(1 row)
postgres=# select extra_desc from pg_settings where name = 'plan_cache_mode';
extra_desc
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Prepared statements can have custom and generic plans, and the planner will attempt to choose which is better. This can be set to override the default behavior.
(1 row)
The default value is “auto” which means the same behavior is in previous versions of PostgreSQL. If you want to force custom plans you can set it to “force_custom_plan” or you can set it to “force_generic_plan” if you want to only see generic plans. Using the same example from above but setting the parameter to “force_custom_plan” we will not see a generic plan anymore:
postgres=# set plan_cache_mode = force_custom_plan;
SET
postgres=# explain (analyze) execute my_stmt ( 'aaa' );
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on demo (cost=0.00..5.75 rows=100 width=8) (actual time=0.034..0.127 rows=100 loops=1)
Filter: (b = 'aaa'::text)
Rows Removed by Filter: 200
Planning Time: 0.209 ms
Execution Time: 0.172 ms
(5 rows)
postgres=# explain (analyze) execute my_stmt ( 'aaa' );
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on demo (cost=0.00..5.75 rows=100 width=8) (actual time=0.152..0.236 rows=100 loops=1)
Filter: (b = 'aaa'::text)
Rows Removed by Filter: 200
Planning Time: 0.170 ms
Execution Time: 0.279 ms
(5 rows)
postgres=# explain (analyze) execute my_stmt ( 'aaa' );
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on demo (cost=0.00..5.75 rows=100 width=8) (actual time=0.031..0.112 rows=100 loops=1)
Filter: (b = 'aaa'::text)
Rows Removed by Filter: 200
Planning Time: 0.170 ms
Execution Time: 0.152 ms
(5 rows)
postgres=# explain (analyze) execute my_stmt ( 'aaa' );
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on demo (cost=0.00..5.75 rows=100 width=8) (actual time=0.029..0.122 rows=100 loops=1)
Filter: (b = 'aaa'::text)
Rows Removed by Filter: 200
Planning Time: 0.206 ms
Execution Time: 0.162 ms
(5 rows)
postgres=# explain (analyze) execute my_stmt ( 'aaa' );
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on demo (cost=0.00..5.75 rows=100 width=8) (actual time=0.038..0.133 rows=100 loops=1)
Filter: (b = 'aaa'::text)
Rows Removed by Filter: 200
Planning Time: 0.200 ms
Execution Time: 0.244 ms
(5 rows)
postgres=# explain (analyze) execute my_stmt ( 'aaa' );
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on demo (cost=0.00..5.75 rows=100 width=8) (actual time=0.032..0.114 rows=100 loops=1)
Filter: (b = 'aaa'::text)
Rows Removed by Filter: 200
Planning Time: 0.169 ms
Execution Time: 0.155 ms
(5 rows)
postgres=# explain (analyze) execute my_stmt ( 'aaa' );
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on demo (cost=0.00..5.75 rows=100 width=8) (actual time=0.034..0.117 rows=100 loops=1)
Filter: (b = 'aaa'::text)
Rows Removed by Filter: 200
Planning Time: 0.301 ms
Execution Time: 0.162 ms
(5 rows)
Nice addition that can help when you have generic plans that actually perform worse than custom plans.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)