Shared Plan Cache
WARNING!
In QHB 1.5.3 release, this feature is experimental and is not recommended for use in a production installation.
The shared plan cache saves CPU time by not recreating SQL query execution plans but storing them in shared memory. This is a distinct feature compared to the plan cache in regular PostgreSQL
Shared Cache Settings in qhb.conf
qhb_plan_cache_size is the size of shared memory buffer. When set to zero, the cache is disabled.
compute_query_id enables built-in query ID calculation. The value must be on or auto, or not set, which is equivalent to auto. When set to off, the cache is disabled.
Setting example:
qhb_plan_cache_size = 100MB # enables a plan cache with a 100MB buffer
compute_query_id = auto
Limitations
Only generic plans that do not depend on parameters are cached (see plan_cache_mode).
The generic plan is formed in the following cases:
- the parameter plan_cache_mode = force_generic_plan is set. It can be
set in the qhb.conf file and in the session parameters using the command
SET plan_cache_mode TO force_generic_plan; - CURSOR_OPT_GENERIC_PLAN is passed to SPI_prepare_cursor
- you type the parameters ($1, $2, etc.) instead of values in the query text when calling SPI_prepare or executing PREPARE
- after 6 runs of the same query with different parameters, a generic plan is formed. QHB uses it if the cost of the generic plan is less than the average cost of the custom plans.
Mandatory condition: using PREPARE, SPI_prepare or extended client-server exchange protocol (PARSE-BIND-EXECUTE). Otherwise, the query is considered one-use, the corresponding property will be set for the plan, and the generic plan will not be generated.
Views
Views allow you to view information about the cache contents.
qhb_sql_cache_info
The qhb_sql_cache_info view contains statistics for the entire cache.
| Column Type | Description |
|---|---|
| total_cache_size int8 | cache size in bytes (from settings) |
| free_cache_size int8 | free space in bytes |
| count int4 | number of stored plans |
| total_plan_size int8 | Used memory in bytes |
| min_plan_size int4 | minimum plan size in bytes |
| max_plan_size int4 | maximum plan size in bytes |
| mean_plan_size int4 | mean plan size in bytes |
qhb_sql_plans_info
The qhb_sql_plans_info view contains a list of saved plans and statistics for each plan.
| Column Type | Description |
|---|---|
| queryid int8 | query ID generated by the kernel (the same ID is used in the pg_store_plans, pg_stat_statements, sr_plan extensions) |
| query text | query text |
| size int4 | plan size in memory |
| plan_time int8 | planning time in nanoseconds |
| cost float8 | execution cost |
| reads int8 | number of plan reads from cache |
| references int4 | number of the plan references (number of backends) |
| executes int8 | number of plan executions |
| total_exec_time int8 | total execution time in nanoseconds |
| min_exec_time int8 | minimum execution time in nanoseconds |
| max_exec_time int8 | maximum execution time in nanoseconds |
| mean_exec_time int8 | mean execution time in nanoseconds |
Since queryid in qhb_sql_plans_info matches queryid in the pg_store_plans, pg_stat_statements views and sr_captured_queries table, they can be linked in queries if the corresponding extensions are enabled.
Showing the Query Plan
To display the stored query plan, you can use the command:
EXPLAIN CACHED_PLAN [ ( parameter [, ...] ) ] queryid
EXPLAIN CACHED_PLAN [ ANALYZE ] [ VERBOSE ] queryid
The syntax is identical to the regular EXPLAIN command, except that you need to add CACHED_PLAN and instead of specifying query you must specify queryid from the qhb_sql_plans_info view.
Metrics
| Name | Description |
|---|---|
| qhb.shared_cache.invokes | number of cache invokes |
| qhb.shared_cache.planned | number of planning |
| qhb.shared_cache.found | number of cache hits, i.e. how many times the requested plan was found |
| qhb.shared_cache.len | number of stored plans |
| qhb.shared_cache.shmem_free | free memory in bytes |