AnalyticDB:Query cache in AnalyticDB for PostgreSQL V6.0

Usage notes

Only AnalyticDB for PostgreSQL instances of V6.3.7.0 or later support the query cache feature. For information about how to view and update the minor version of an AnalyticDB for PostgreSQL instance, see View the minor version of an instance and Update the minor version of an instance.

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

• You can use the query cache feature only if you set the transaction isolation level to READ COMMITTED.

• You can use the query cache feature only if you set the Grand Unified Configuration (GUC) parameters rds_uppercase_colname and gp_select_invisible to off.

• You can query the cached data only if you enable the query cache feature for all tables in the query.

• You can use the query cache feature only if the frontend protocol version supported by the libpq library is 3.0 or later.

• If you execute a query statement after data is modified within the same transaction block, query results cannot be cached.

• You cannot use the query cache feature for temporary tables, views, materialized views, system tables, unlogged tables, external tables, or volatile or immutable functions.

• You cannot use the query cache feature for queries on partitions.

• You cannot use the query cache feature for queries when multiple coordinator nodes exist.

• You cannot use the query cache feature if the size of a query result exceeds 7.5 KB.

• You cannot use the query cache feature if more than 32 tables are involved in a single query.

• You cannot use the query cache feature if cursors are used in extended queries.

Enable the query cache feature for an instance

By default, the query cache feature is disabled because the feature is suitable only if queries have high temporal locality. To enable the query cache feature for an AnalyticDB for PostgreSQL instance, Submit a ticket.

Enable or disable the query cache feature for a session

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

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

SET rds_session_use_query_cache = on;

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

SET rds_session_use_query_cache = off;

Enable or disable the query cache feature for a table

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

When you create a table, execute the following statement to enable the query cache feature for the table:

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

If you did not enable the query cache feature when you created a table, execute the following statement to enable the query cache feature for the table:

ALTER TABLE table_name SET (querycache_enabled=on);

If you no longer require the query cache feature for a table, execute the following statement to disable the query cache feature for the table:

ALTER TABLE table_name SET (querycache_enabled=off);

Modify the validity period of cached query results

If DDL or DML statements are executed on a table, the cached query results of the table become invalid. This ensures that the correct query results are returned and prevents invalid query results from being returned. AnalyticDB for PostgreSQL uses the multiversion concurrency control (MVCC) mechanism, and the query cache feature stores only the most recent query results. As a result, invalid query results may be returned because the cached query results are not updated at the earliest opportunity. For example, invalid query results may occur if read and write transactions are concurrently committed for a table.

By default, the cached query results remain valid for 10 minutes. If an identical query is executed after the query results are cached for more than 10 minutes, the system re-executes the query instead of returning the cached query results.

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

Performance evaluation

This section evaluates the performance of the query cache feature 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 approximately 10 ms. If the query cache feature is enabled and the cache hit rate is 100%, the query performance improves by approximately 6.5 times. Even if the cache hit rate is 0%, the query performance when the query cache feature is enabled is not significantly lower than the performance when the query cache feature is disabled. The amount of time required by the query cache feature to complete a query does not change significantly and does not exceed 20 ms if cache replacement exists.

OLAP

The following table describes the test results of TPC-H and TPC-DS queries on 10 GB of data.

In OLAP scenarios, the performance of TPC-H and TPC-DS queries is significantly improved when the query cache feature is enabled. For example, the performance of TPC-H Q1 is improved by more than 1,000 times when cached query results are matched. However, specific TPC-H and TPC-DS query results that exceed the maximum query cache size of 7.5 KB are not cached. Overall, the test does not indicate a significant improvement in performance.