AnalyticDB for PostgreSQL:Query cache

Precautions

The query cache feature is available only in AnalyticDB for PostgreSQL V6.3.7.0 or later. For information about how to query and update the minor engine version of an AnalyticDB for PostgreSQL instance, see View the minor engine version and Update the minor engine version.

The following limits apply when you use the query cache feature in AnalyticDB for PostgreSQL:

• Query cache is supported only when the transaction isolation level is read committed (READ-COMMITTED).
• Query cache is supported only when the Grand Unified Configuration (GUC) parameters rds_uppercase_colname and gp_select_invisible are set to off.
• Cached data can be queried only when all tables in the query have the query cache feature enabled.
• Query cache is supported only when the frontend protocol version of libpq is 3.0 or later.
• If a query within a transaction block has been modified, its query results cannot be stored in the query cache.
• Query cache is not supported for temporary tables, views, materialized views, system tables, unlogged tables, external tables, or volatile or immutable functions.
• Query cache is not supported for queries on child partitioned tables.
• Query cache is not supported for queries when multiple coordinator nodes exist.
• Query cache is not supported if the result set exceeds 7.5 KB in size.
• Query cache is not supported when more than 32 tables are involved in a single query.
• Query cache is not supported if cursors are used in extended queries.

Enable query cache for an instance

By default, query cache is disabled because it is suitable only when a query has high temporal locality. To enable query cache for an instance, Submit a ticket.

Enable or disable query cache for a session

You can use the rds_session_use_query_cache parameter to enable or disable query cache for a session.

Execute the following statement to enable query cache for a session:

SET rds_session_use_query_cache = on;

Execute the following statement to disable query cache for a session:

SET rds_session_use_query_cache = off;

Enable or disable query cache for a table

You can use the querycache_enabled parameter to enable or disable query cache for a table.

For a new table, execute the following statement to enable query cache:

CREATE TABLE table_name (c1 int, c2 int) WITH (querycache_enabled=on);

For a table that did not have query cache enabled when the table was created, execute the following statement to enable query cache:

ALTER TABLE table_name SET (querycache_enabled=on);

For a table that no longer requires query cache, execute the following statement to disable query cache:

ALTER TABLE table_name SET (querycache_enabled=off);

Modify the validity period of query cache

When DDL or DML statements are being executed, query results stored in the query cache expire. This prevents the expired query results from being returned. However, AnalyticDB for PostgreSQL uses the multiversion concurrency control (MVCC) mechanism, and the query cache stores only the latest query results. As a result, expired query results are returned in scenarios such as those in which concurrent read and write operations exist.

By default, results stored in the query cache remain valid for 10 minutes. If an identical query is made after the query results have been cached for longer than 10 minutes, the query is performed normally and the cached results for the query are not returned.

To prevent expired query results from being returned, you can Submit a ticket to modify the validity period of query cache.

Performance evaluation

This section evaluates the performance of query cache in two load scenarios: online transaction processing (OLTP) and online analytical processing (OLAP).

OLTP

The following table describes the test results for point queries with indexes.

In OLTP scenarios, a normal query has a latency of about 10 ms. In the case that the cache hit rate is 100%, the query performance improves by about 6.5 times when query cache is used. Even if the cache hit rate is 0%, the query performance when query cache is used is not significantly lower than that when query cache is not used. The absolute amount of time required to complete a query with query cache enabled does not change significantly and does not exceed 20 ms when cache replacement exists.

OLAP

The following table describes the test results of queries on 10 GB of data.

In OLAP scenarios, the query performance when query cache is used is significantly higher than that when query cache is not used. For example, in the TPC-H test, the performance of Q1 query improves by more than 1,000 times when the cache hit rate is not 0%. In the TPC-DS test, specific query results exceed the maximum size of 7.5 KB allowed for the query cache and are not cached. As a result, the test result does not present a significant performance improvement.