Here is a small test case to demonstrate plan_cache_mode, a PostgreSQL 12 feature available in YugabyteDB 2.25
yugabyte=> \c
psql (16.2, server 15.2-YB-2.25.0.0-b0)
You are now connected to database "yugabyte" as user "yugabyte".
yugabyte=> drop table if exists t;
DROP TABLE
yugabyte=> deallocate all;
show plan_cache_mode;
DEALLOCATE ALL
yugabyte=> show plan_cache_mode;
plan_cache_mode
-----------------
auto
(1 row)
I create a small table and a prepared query:
yugabyte=> create table t
as select generate_series(1,100000) val
;
SELECT 100000
yugabyte=> prepare query(int)
as select * from t where val=$1
;
PREPARE
yugabyte=> explain (analyze) execute query(42);
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on t (cost=180.00..1098.09 rows=5 width=4) (actual time=56.087..56.092 rows=1 loops=1)
Storage Filter: (val = 42)
Planning Time: 5.451 ms
Execution Time: 56.162 ms
Peak Memory Usage: 48 kB
(5 rows)
The execution plan shows that the parameter $1 was replaced with the value 42 before query planning.
I execute the same prepared statement five more times:
yugabyte=> \watch count=5
Tue 14 Jan 2025 01:45:56 PM GMT (every 2s)
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on t (cost=180.00..1098.09 rows=5 width=4) (actual time=88.165..88.170 rows=1 loops=1)
Storage Filter: (val = 42)
Planning Time: 0.091 ms
Execution Time: 88.220 ms
Peak Memory Usage: 24 kB
(5 rows)
Tue 14 Jan 2025 01:45:58 PM GMT (every 2s)
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on t (cost=180.00..1098.09 rows=5 width=4) (actual time=22.839..22.845 rows=1 loops=1)
Storage Filter: (val = 42)
Planning Time: 0.128 ms
Execution Time: 22.900 ms
Peak Memory Usage: 24 kB
(5 rows)
Tue 14 Jan 2025 01:46:00 PM GMT (every 2s)
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on t (cost=180.00..1098.09 rows=5 width=4) (actual time=36.821..36.826 rows=1 loops=1)
Storage Filter: (val = 42)
Planning Time: 0.133 ms
Execution Time: 36.878 ms
Peak Memory Usage: 24 kB
(5 rows)
Tue 14 Jan 2025 01:46:02 PM GMT (every 2s)
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on t (cost=180.00..1098.09 rows=5 width=4) (actual time=22.879..22.885 rows=1 loops=1)
Storage Filter: (val = 42)
Planning Time: 0.093 ms
Execution Time: 22.935 ms
Peak Memory Usage: 24 kB
(5 rows)
Tue 14 Jan 2025 01:46:04 PM GMT (every 2s)
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on t (cost=180.00..1098.09 rows=5 width=4) (actual time=22.278..22.283 rows=1 loops=1)
Storage Filter: (val = $1)
Planning Time: 0.107 ms
Execution Time: 22.331 ms
Peak Memory Usage: 24 kB
(5 rows)
After five executions, the query planner evaluates an execution plan that can be used for any value (val = $1 instead of val = 42). SQL Server would call that disabling parameter sniffing, and Oracle would call that disabling bind peeking. The plan may not be the best one for a specific value, but it will be stable and avoid spending more planning time in future executions:
yugabyte=> explain (analyze) execute query(42);
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on t (cost=180.00..1098.09 rows=5 width=4) (actual time=22.857..22.862 rows=1 loops=1)
Storage Filter: (val = $1)
Planning Time: 0.012 ms
Execution Time: 22.919 ms
Peak Memory Usage: 24 kB
(5 rows)
The difference in planning time is small for a simple query without indexes, but it matters for more complex queries.
For the demonstration, I'll create an index that is faster but works only for some values, and then cannot be used by a generic plan:
yugabyte=> create index on t(val) where val<50;
CREATE INDEX
My prepared statement still use the generic plan:
yugabyte=> explain (analyze) execute query(42);
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on t (cost=180.00..1098.09 rows=5 width=4) (actual time=22.647..22.653 rows=1 loops=1)
Storage Filter: (val = $1)
Planning Time: 11.583 ms
Execution Time: 22.719 ms
Peak Memory Usage: 24 kB
(5 rows)
With plan_cache_mode I can force a custom or generic plan rather than relying on the automatic mechanism:
yugabyte=> set plan_cache_mode = force_custom_plan;
SET
yugabyte=> explain execute query(42);
QUERY PLAN
----------------------------------------------------------------------------
Index Only Scan using t_val_idx on t (cost=180.00..555.06 rows=5 width=4)
Index Cond: (val = 42)
(2 rows)
yugabyte=> set plan_cache_mode = force_generic_plan;
SET
yugabyte=> explain execute query(42);
QUERY PLAN
------------------------------------------------------
Seq Scan on t (cost=180.00..1098.09 rows=5 width=4)
Storage Filter: (val = $1)
(2 rows)
Given the cost of the custom plan for this value, it is better to use a custom plan rather than switching to a generic one. When using the auto plan cache mode, the query planner evaluates a generic plan after five executions but can decide to keep the custom plan if the cost is lower so that the estimated total execution (planning plus execution) remains cheaper than the execution of the generic plan:
yugabyte=> set plan_cache_mode = auto;
SET
yugabyte=> explain execute query(42);
QUERY PLAN
----------------------------------------------------------------------------
Index Only Scan using t_val_idx on t (cost=180.00..555.06 rows=5 width=4)
Index Cond: (val = 42)
(2 rows)
yugabyte=> \watch count=10 0.01
Tue 14 Jan 2025 01:56:29 PM GMT (every 0.01s)
QUERY PLAN
----------------------------------------------------------------------------
Index Only Scan using t_val_idx on t (cost=180.00..555.06 rows=5 width=4)
Index Cond: (val = 42)
(2 rows)
Tue 14 Jan 2025 01:56:29 PM GMT (every 0.01s)
QUERY PLAN
----------------------------------------------------------------------------
Index Only Scan using t_val_idx on t (cost=180.00..555.06 rows=5 width=4)
Index Cond: (val = 42)
(2 rows)
Tue 14 Jan 2025 01:56:29 PM GMT (every 0.01s)
QUERY PLAN
----------------------------------------------------------------------------
Index Only Scan using t_val_idx on t (cost=180.00..555.06 rows=5 width=4)
Index Cond: (val = 42)
(2 rows)
Tue 14 Jan 2025 01:56:29 PM GMT (every 0.01s)
QUERY PLAN
----------------------------------------------------------------------------
Index Only Scan using t_val_idx on t (cost=180.00..555.06 rows=5 width=4)
Index Cond: (val = 42)
(2 rows)
Tue 14 Jan 2025 01:56:29 PM GMT (every 0.01s)
QUERY PLAN
----------------------------------------------------------------------------
Index Only Scan using t_val_idx on t (cost=180.00..555.06 rows=5 width=4)
Index Cond: (val = 42)
(2 rows)
Tue 14 Jan 2025 01:56:29 PM GMT (every 0.01s)
QUERY PLAN
----------------------------------------------------------------------------
Index Only Scan using t_val_idx on t (cost=180.00..555.06 rows=5 width=4)
Index Cond: (val = 42)
(2 rows)
Tue 14 Jan 2025 01:56:29 PM GMT (every 0.01s)
QUERY PLAN
----------------------------------------------------------------------------
Index Only Scan using t_val_idx on t (cost=180.00..555.06 rows=5 width=4)
Index Cond: (val = 42)
(2 rows)
Tue 14 Jan 2025 01:56:29 PM GMT (every 0.01s)
QUERY PLAN
----------------------------------------------------------------------------
Index Only Scan using t_val_idx on t (cost=180.00..555.06 rows=5 width=4)
Index Cond: (val = 42)
(2 rows)
Tue 14 Jan 2025 01:56:29 PM GMT (every 0.01s)
QUERY PLAN
----------------------------------------------------------------------------
Index Only Scan using t_val_idx on t (cost=180.00..555.06 rows=5 width=4)
Index Cond: (val = 42)
(2 rows)
Tue 14 Jan 2025 01:56:29 PM GMT (every 0.01s)
QUERY PLAN
----------------------------------------------------------------------------
Index Only Scan using t_val_idx on t (cost=180.00..555.06 rows=5 width=4)
Index Cond: (val = 42)
(2 rows)
The query planner never switched to the generic plan because of its higher estimated cost. I made this example with a partial index, it also happens when the custom plan allows partition pruning.
A final note: the five executions are hardcoded in PostgreSQL, but YugabyteDB made it configurable:
List of configuration parameters
Parameter | Value
----------------------------------------------+-------
plan_cache_mode | auto
yb_planner_custom_plan_for_partition_pruning | on
yb_test_planner_custom_plan_threshold | 5
(3 rows)
It has a test in its name because the main reason is regression tests, where you want to see the behavior without executing it multiple times.
You may see a generic plan later with the JDBC driver because the five executions kick in only when the statement is prepared servrr-side, and pgJDBC doesn't do it immediately. It waits for the fith client-side execution before a server-side prepared statement is created, so it's finally the tenth execution that can switch to a generic plan. The client-side uses the same value, five, but it is configurable with the prepareThreshold JDBC property.
Top comments (0)