Manage query plans

This document describes how to improve your query plan performance using query plan management in AlloyDB for PostgreSQL. Query plan management continuously tracks all query plans and their execution statistics in your database. After you review the queries and costs associated with them, you can approve a plan, which is consistently applied for a given query. This approach ensures selection of a cost-efficient query plan leading to improved query performance.

How it works

In PostgreSQL, a query optimizer selects an execution plan for each query based on estimated costs. Many factors influence the cost of query—for example, query parameters, query complexity, table size, available indexes, and system resources.

As query parameters might change with each query run, selecting a query plan dynamically might not always yield optimal results. When processing a query, the optimizer evaluates various execution plans and tries to choose the most cost-effective plan.

A parameter change might result in a plan change. While the selected plan is usually the most cost-effective option, there might be instances where a less cost-efficient plan is chosen, which results in poor query performance. Query plan management helps you understand the patterns and plans produced by the optimizer, and lets you view each plan for informed decision-making.

The two main components of query plan management are as follows:

Query plan repository: When you enable query plan management for your database, the plan repository starts tracking historical plans and execution statistics on your database. Query plan repository provides observability about the query plan performance.
Plan management: After you review the available plans, the plan management component lets you approve one or more plans for a specific query template. Query plan management tracks these approved plans and ensures that when the query is subsequently executed, the query optimizer uses only one of the approved plans. If multiple plans are approved, then AlloyDB selects and executes the plan with the lowest estimated cost.

Before you begin

Set the google_plan_management.enabled database flag to on. For more information, see Configure database flags.
Create the google_plan_management extension on the database. For more information, see Enable an extension.
Grant google_plan_management_role to users of the database who want to use query plan management and manage query plans.
1. In the Google Cloud console, go to the AlloyDB Clusters page.
  
  Go to Clusters
2. Click the required instance.
3. Click AlloyDB Studio, and then click the Editor 1 tab.
4. Enter the following query:
```
GRANT google_plan_management_role TO DATABASE_USER;
```
  Replace DATABASE_USER with the user you want to grant the role to.
5. Click Run.

View tracked query plans

Query plan management provides a query plan view that displays all tracked query plans, their execution times, and other information. A tracked query plan is a query plan that is generated by the optimizer and is stored in the plan repository.

To view historical tracked plans, run the following query:

SELECT * FROM google_plan_management.tracked_plans_view;

The query response is similar to the following:

db_id                | 5
db_name              | postgres
user_id              | 16392
user_name            | postgres
logical_query_id     | 15480571796188147798
plan_id              | 4740866759615354783
query                | SELECT c1, c2, c3 FROM t1 WHERE c1 = 1;
plan                 | Seq Scan on public.t1                                                                                                                                                            +
                     |   Output: c1, c2, c3                                                                                                                                                             +
                     |   Filter: (t1.c1 = ?)
total_execution_time | 0.003937501
num_executions       | 1
creation_time        | 2024-11-06 16:52:25.200737+00
last_used_time       | 2024-11-06 16:52:25.200737+00

Disable query plan tracking

If you don't want query plan management to track the query plans generated by the optimizer, then you must disable the google_plan_management.enable_track_plans database flag. This flag is enabled by default, and we recommend leaving it on. For more information, see Configure database flags.

View managed plans

You can view all the queries and plans that are managed by query plan management, including approved and denied plans.

To view managed plans, run the following query:

SELECT * FROM google_plan_management.managed_plans_view;

The query response is similar to the following:

db_id            | 5
db_name          | postgres
user_id          | 16392
user_name        | postgres
logical_query_id | 15480571796188147798
plan_id          | 4740866759615354783
status           | approved

Approve a plan

The optimizer selects a query plan dynamically, which means it might choose different query plans for the same query at different times. To enforce consistent plan selection, you can use query plan management to approve one or more query plans for a given query.

If you approve multiple plans, then query plan management compares all approved plans and selects the most cost-efficient plan for query execution.

To evaluate and approve a plan for a query, follow these steps:

View the tracked plans that the optimizer generated and identify the logical_query_id in the response.
Review all the plans that are generated for the logical_query_id. You can calculate the average execution time for each plan using the total_execution_time and num_executions values, and then decide the best plan for your query.

The plan column includes other details such as index used or sorting method used that can help you decide on a query plan.
Approve the plan that you want to apply to the query by running the following query:
```
  SELECT google_plan_management.approve_plan(QUERY_ID, PLAN_ID);
```
Replace the following:
- QUERY_ID: the unique logical_query_id for the query. Each query ID might be associated with multiple plan IDs.
- PLAN_ID: the unique plan_id for the query.

Deny a plan

You can deny any approved plan for a query and stop query plan management from applying the plan to the query. Denied plans aren't deleted, and are available in the list of tracked plans.

To deny an approved plan, run the following query:

    SELECT google_plan_management.deny_plan(QUERY_ID, PLAN_ID);

Delete an approved plan

You can delete an approved plan from the plan repository. When you delete an approved plan, that plan no longer appears in the list of managed plans.

To delete an approved plan, run the following query:

    SELECT google_plan_management.update_plan_status(QUERY_ID, PLAN_ID, 'delete');

Temporarily stop using approved plans

If you want to temporarily stop using approved plans for your queries, then you must disable the google_plan_management.enable_steer_plans database flag. This flag is enabled by default. For more information, see Configure database flags.

Limitations

You can't use query plan management in partitioned tables or grouping sets.
Query plan management is supported only on the primary instance.
Query plan repository can store up to 100 K unique plans, and doesn't provide a retention policy.