what are the features of global plan cache - PolarDB - Alibaba Cloud Documentation Center

This topic describes the global plan cache (GPC) feature of PolarDB for PostgreSQL.

Background information

In previous versions of PolarDB, the plan cache was bound to prepared statements. This approach had two drawbacks:

Plan caches were isolated to individual connections and could not be shared.
Each connection maintained its own plan cache, which resulted in high memory usage.

PolarDB for PostgreSQL introduces the GPC feature to address these issues by allowing different connections to share the same plan cache.

Plans can be shared between different prepared statements and connections. For applications with many different SQL statements, GPC can significantly reduce memory usage and lower the risk of out-of-memory (OOM) errors. This efficient plan caching mechanism also reduces the cost of generating execution plans, which improves performance.

Plans can be shared only if their query keys are the same. A query key consists of the following parts:

The query text.
The database ID.
The object search path.
The user ID.

Applicability

The feature is available for the following versions of PolarDB for PostgreSQL:
- PostgreSQL 18 (minor engine version 2.0.18.0.1.0 or later)
- PostgreSQL 17 (minor engine version 2.0.17.2.1.0 or later)
- PostgreSQL 16 (minor engine version 2.0.16.3.1.1 or later)
- PostgreSQL 15 (minor engine version 2.0.15.7.1.1 or later)
- PostgreSQL 14 (minor engine version 2.0.14.9.15.0 or later)
- PostgreSQL 11 (minor engine version 2.0.11.9.28.0 or later)
Note
You can view the minor engine version number in the console or run the SHOW polardb_version; statement. If your cluster does not meet the minor engine version requirement, upgrade the minor engine version.
The GPC feature is enabled by default on clusters that meet the version requirements.

Limits

GPC supports only prepared statements. Plan caching is not supported in PL/SQL scenarios.
Only SELECT, INSERT, UPDATE, and DELETE statements are supported.
Temporary tables are not supported.

Parameters

Parameter	Description
polar_gpc_mem	Sets the memory size for GPC. Unit: MB. The default value is 30 MB. The value cannot exceed the size of `shared_buffer`. Note Restart the cluster for the parameter modification to take effect. If `polar_gpc_mem` is less than or equal to 0, the GPC feature is disabled. If `polar_gpc_mem` is greater than 0, the cluster reserves the specified amount of shared memory at startup. If shared memory is insufficient, new plan caches are temporarily stored locally. When infrequently used or invalid GPC entries are cleared, shared memory is released. The system then tries to move the local plan caches to the GPC.
polar_enable_gpc_level	The level at which the GPC feature is enabled. This parameter can be modified dynamically. Valid values: 0 (Default): GPC is not used. 1: GPC is used only on read-only (RO) nodes. 2: GPC is used on both the primary (RW) and read-only nodes. Note This parameter must be used with `polar_gpc_mem`. GPC works only when `polar_gpc_mem` is greater than 0 and `polar_enable_gpc_level` is greater than 0. If `polar_gpc_mem` is greater than 0 and `polar_enable_gpc_level` is 0, existing queries can continue to use GPC, but new queries cannot.
polar_gpc_clean_timeout	The time interval for clearing infrequently used GPC entries. This parameter can be modified dynamically. Unit: seconds. The default value is 1800 seconds. The value can be from 0 seconds to 24 hours.
polar_worker.gpc_clear_interval	The time interval for clearing invalid GPC entries. This parameter can be modified dynamically. Unit: seconds. The default value is 60 seconds. The maximum value is (2^32 - 1)/1000.
polar_gpc_clean_max	The number of GPC entries to clear at a time. This parameter can be modified dynamically. The default value is 100. The value range is 10 to 10000.
polar_gpc_partitions	The number of hash tables used to store GPC entries. The default value is 32. The value range is 1 to 1024. Note Restart the cluster for the parameter modification to take effect.
polar_gpc_entries	The maximum number of entries in each hash table. The default value is 1024. The value range is 1 to 10000. Note Restart the cluster for the parameter modification to take effect.

Usage guide

The monitoring views and functions for the GPC feature are included in the polar_gpc extension. You can run the following command to create this extension.

CREATE EXTENSION IF NOT EXISTS polar_gpc;

Views

polar_stat_gpc - View the overall usage of GPC

You can check the overall usage of GPC from the polar_stat_gpc view as follows:

SELECT * FROM polar_stat_gpc;

Key metrics in the polar_stat_gpc view include the following:

get: The number of attempts to retrieve a matching GPC entry.
hit: The number of times a matching GPC entry was found.
store: The number of times a plan was successfully saved to the GPC.
store_failed: The number of times that a plan failed to be stored because of temporary out-of-memory errors in the GPC. A frequent increase in this value indicates that the polar_gpc_mem parameter is set too low.
store_exists: The number of times the system tried to add a local plan cache to the GPC but found that another session had already added the plan.

polar_gpc_plan - View the memory usage of each GPC entry

You can query the polar_gpc_plan view to see the memory usage of each GPC entry. For example:

SELECT * FROM polar_gpc_plan;

Key metrics in the polar_gpc_plan view include the following:

plan_id: The ID of the execution plan.
stmt_name: The name of the prepared statement.
query: The query statement.
used_cnt: The number of times the plan has been used.
last_use_time: The time when the GPC entry was last used.
is_valid: Specifies whether the plan is valid.

polar_gpc_plan_mcxt - View the MemoryContext information for each GPC entry

You can query the polar_gpc_plan_mcxt view to see the MemoryContext information for each GPC entry. For example:

SELECT * FROM polar_gpc_plan_mcxt;

Key metrics in the polar_gpc_plan_mcxt view include the following:

plan_id: The ID of the execution plan.
mcxt_name: The name of the MemoryContext.
totalspace: The total memory space.
freespace: The available memory space.
used: The used memory space.
nblocks: The number of blocks.

polar_gpc_plan_key - View the GPC key for each GPC entry

You can query the polar_gpc_plan_key view to see the GPC key for each GPC entry. The key is used to find matching GPC entries. For example:

SELECT * FROM polar_gpc_plan_key;

Key metrics in the polar_gpc_plan_key view include the following:

plan_id: The ID of the plan.
query: The query statement.
dbid: The ID of the database.
pid: The process ID.
num_params: The number of parameters in the query statement.
search_path: The search path.
role_id: The user ID.

polar_prepared_statement - View information about all prepared statements in the GPC

You can query the polar_prepared_statement view to see information about all prepared statements in the GPC. For example:

SELECT * FROM polar_prepared_statement;

Key metrics in the polar_prepared_statement view include the following:

is_saved: Indicates whether the execution plan of the prepared statement is saved to the GPC.
is_valid: Indicates whether the plan is valid.
cacheable: Indicates whether the plan can be cached.

Functions

polar_gpc_evict_invalid_gpc - Manually clear invalid GPC entries

You can use the polar_gpc_evict_invalid_gpc function to manually clear invalid GPC entries. For example:

SELECT polar_gpc_evict_invalid_gpc();

If you do not call this function, the system automatically clears invalid GPC entries at the interval specified by the $polar_worker.gpc_clear_interval parameter.

polar_gpc_evict_live_gpc - Manually evict infrequently used GPC entries

You can use the polar_gpc_evict_live_gpc function to manually evict infrequently used GPC entries. For example:

SELECT polar_gpc_evict_live_gpc();

If you do not call this function, the system automatically evicts infrequently used GPC entries at the interval specified by the $polar_worker.gpc_clear_interval parameter.