> [!WARNING] > > **Preview > --- query plan management** > > > This feature is > > subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the > [Service Specific > Terms](https://docs.cloud.google.com/terms/service-terms#1). > > Pre-GA features are available "as is" and might have limited support. > > For more information, see the > [launch stage descriptions](https://cloud.google.com/products/#product-launch-stages). Query plan management in AlloyDB for PostgreSQL lets you improve and stabilize query performance by controlling which execution plans the query optimizer uses for your queries. Query plan management continuously tracks all query plans and their execution statistics in your database. After you review the plans and their costs, you can approve the plans that you want AlloyDB to use for a query. This helps you prevent slowdowns that occur when the optimizer switches to a less cost-efficient plan. ## 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. > [!NOTE] > **Note:** For heavy workloads, enabling query plan management might have slight resource overhead. 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](https://docs.cloud.google.com/alloydb/docs/instance-configure-database-flags#console). - Create the `google_plan_management` extension on the database. For more information, see [Enable an extension](https://docs.cloud.google.com/alloydb/docs/instance-configure-extensions#enable). - 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](https://console.cloud.google.com/alloydb/clusters) 2. Click the required instance. 3. Click **AlloyDB Studio** , and then click the **Editor 1** tab. 4. Enter the following query: ```sql 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: ```sql 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](https://docs.cloud.google.com/alloydb/docs/instance-configure-database-flags#console). ## 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: ```sql 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. > [!NOTE] > **Note:** Tracked plans are not enforced until you approve them. Any plan that is neither approved or denied, might still be used by the optimizer. We recommend explicitly approving or denying a plan for consistent plan selection. 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: 1. [View the tracked plans](https://docs.cloud.google.com/alloydb/docs/query-plan-management.md.txt#view-tracked-plans) that the optimizer generated and identify the `logical_query_id` in the response. 2. 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. 3. Approve the plan that you want to apply to the query by running the following query: ```sql 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: ```sql 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: ```sql 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](https://docs.cloud.google.com/alloydb/docs/instance-configure-database-flags#console). ## 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. ## What's next - [Learn about the AlloyDB for PostgreSQL columnar engine](https://docs.cloud.google.com/alloydb/docs/columnar-engine/about.md.txt) - [Use the index advisor with Query Insights](https://docs.cloud.google.com/alloydb/docs/use-index-advisor-with-query-insights.md.txt)