Create and manage query plan patches

This page describes how to create and manage query plan patches in AlloyDB for PostgreSQL.

A query plan patch is an association between a query and a set of hints that let you specify the details of the query plan. A hint specifies additional information about the preferred final execution plan for the query. For example, when you scan a table in the query, use an index scan instead of other types of scans, such as a sequential scan.

To limit the final plan choice within the specification of the hints, the query planner first applies the hints to the query while generating its execution plan. The hints are then automatically applied whenever the query is subsequently issued. This approach lets you force different query plans from the planner. For example, you can use hints to force an index scan on certain tables or to force a specific join order among multiple tables.

The AlloyDB query plan patch supports all the hints from the open source pg_hint_plan extension.

Additionally, AlloyDB supports the following hints for the columnar engine:

ColumnarScan(table): Forces a columnar scan on the table.
NoColumnarScan(table): Disables columnar scan on the table.

AlloyDB lets you create plan patches for both parameterized queries and non-parameterized queries. In this page, non-parameterized queries are referred to as parameter sensitive queries.

Workflow

Using a query plan patch involves the following steps:

Identify the query for which you want to create a plan patch.
Create a plan patch with hints to be applied when the query is next executed.
Verify the application of the plan patch.

This page uses the following table and index for examples:

CREATE TABLE t(a INT, b INT);
CREATE INDEX t_idx1 ON t(a);

  DROP EXTENSION IF EXISTS google_auto_hints;

To continue using the query plan patches that you created using an earlier version, recreate them by following the instructions in this page.

Before you begin

Enable the query plan patch feature on your instance. Set the alloydb.enable_query_plan_patch flag to on. You can enable this flag at the server-wide level or at the session level. To minimize overhead that might result from using this feature, enable this flag only at the session level.

For more information, see Configure an instance's database flags.

Note: To force the same query plan on your read pool instance, enable the query plan patch feature on the read pool instance.

To verify that the flag is enabled, run the show alloydb.enable_query_plan_patch; command. If the flag is enabled, the output returns "on".
For each database in which you want to use query plan patches, create an extension in the database from the AlloyDB primary instance as the alloydbsuperuser or the postgres user:
```
CREATE EXTENSION google_auto_hints CASCADE;
```

Required roles

To get the permissions that you need to create and manage query plan patches, ask your administrator to grant you the following Identity and Access Management (IAM) roles:

alloydbsuperuser role

While the default permission only allows the user with the alloydbsuperuser role to create plan patches, you can optionally grant the write permission to the other users or roles of the database so that they can create plan patches.

GRANT INSERT,DELETE,UPDATE ON hint_plan.plan_patches, hint_plan.hints TO role_name;
GRANT USAGE ON SEQUENCE hint_plan.hints_id_seq, hint_plan.plan_patches_id_seq TO role_name;

Identify the query

You can use the query ID to identify the query whose default plan needs tuning. The query ID becomes available after at least one execution of the query.

Use the following methods to identify the query ID:

Run the EXPLAIN (VERBOSE) command, as shown in the following example:

EXPLAIN (VERBOSE) SELECT * FROM t WHERE a = 99;
                        QUERY PLAN
----------------------------------------------------------
Seq Scan on public.t  (cost=0.00..38.25 rows=11 width=8)
  Output: a, b
  Filter: (t.a = 99)
Query Identifier: -6875839275481643436

In the output, the query ID is -6875839275481643436.

Query the pg_stat_statements view.

If you enabled the pg_stat_statements extension, you can find the query ID by querying the pg_stat_statements view, as shown in the following example:
```
select query, queryid from pg_stat_statements;
```

Create a query plan patch

To create a query plan patch, use the google_create_plan_patch() function, which creates an association between the query and the hints in the database.

SELECT google_create_plan_patch(
PLAN_PATCH_NAME=>'PLAN_PATCH_NAME',
SQL_ID=>QUERY_ID,
SQL_TEXT=>QUERY_TEXT,
APPLICATION_NAME=>'APPLICATION_NAME',
HINTS=>'HINTS',
DISABLED=>DISABLED);

Replace the following:

PLAN_PATCH_NAME: a name for the plan patch. This must be unique within the database.
SQL_ID (Optional): query ID of the query for which you are creating the plan patch.

You can use either the query ID or the query text—the SQL_TEXT parameter—to create a plan patch. However, we recommend that you use the query ID to create a plan patch because AlloyDB automatically locates the normalized query text based on the query ID.
SQL_TEXT (Optional): query text of the query for which you are creating the plan patch.

When you use the query text, the text must be the same as the intended query, except for the literal and constant values in the query. Any mismatch, including case difference, can result in the plan patch not being applied. To learn how to create plan patches for queries with literals and constants, see Create a parameter sensitive query plan patch.
APPLICATION_NAME (Optional): name of the session client application for which you want to use the plan patch. An empty string lets you apply the plan patch to the query regardless of the client application issuing the query.
HINTS: a space-separated list of the hints for the query.
DISABLED (Optional): BOOL. If TRUE, initially creates the plan patch initially as disabled.

Example:

SELECT google_create_plan_patch(
PLAN_PATCH_NAME=>'my_hint1',
SQL_ID=>-6875839275481643436,
SQL_TEXT=>NULL,
APPLICATION_NAME=>'',
HINTS=>'IndexScan(t)',
DISABLED=>NULL);

This query creates a plan patch named my_hint1. Its hint IndexScan(t) is applied by the planner to force an index scan on the table t on the next run of this example query.

After creating a plan patch, you can use the google_query_plan_patch_view to confirm if the plan patch is created, as shown in the following example:

postgres=>\x
postgres=>select * from google_query_plan_patch_view limit 1;
-[ RECORD 1 ]-----+-----------------------------
plan_patch_name | my_hint1
sql_id | -6875839275481643436
id | 9
query_string | SELECT * FROM t WHERE a = ?;
application_name |
hints | IndexScan(t)
disabled | f

After the plan patch is created on the primary instance, it's automatically applied to the associated queries on the read pool instance, provided that you enabled the query plan patch feature on the read pool instance as well.

Create a parameter-sensitive query plan patch

By default, when a plan patch is created for a query, the associated query text is normalized by replacing any literal and constant value in the query text with a parameter marker, such as ?. The plan patch is then used for that normalized query even with a different value for the parameter marker.

For example, running the following query allows another query, such as SELECT * FROM t WHERE a = 99;, to use the plan patch my_hint2 by default.

SELECT google_create_plan_patch(
  PLAN_PATCH_NAME=>'my_hint2',
  SQL_ID=>NULL,
  SQL_TEXT=>'SELECT * FROM t WHERE a = ?;',
  APPLICATION_NAME=>'',
  HINTS=>'SeqScan(t)',
  DISABLED=>NULL);

Then a query, such as SELECT * FROM t WHERE a = 99;, can use the plan patch my_hint2 by default.

AlloyDB also lets you create a plan patch for non-parameterized query texts, in which each literal and constant value in the query text is significant when matching queries.

When you apply a parameter-sensitive plan patch, two queries which only differ in the corresponding literal or constant values are also considered different. If you want to force plans for both queries, you must create separate plan patches for each query. However, you can use different hints for the two plan patches.

To create a parameter-sensitive plan patch, set the SENSITIVE_TO_PARAM parameter of the google_create_plan_patch() function to TRUE, as shown in the following example:

SELECT google_create_plan_patch(
PLAN_PATCH_NAME=>'my_hint3',
SQL_ID=>NULL,
SQL_TEXT=>'SELECT * FROM t WHERE a = 88;',
APPLICATION_NAME=>'',
HINTS=>'IndexScan(t)',
DISABLED=>NULL,
SENSITIVE_TO_PARAM=>TRUE);

The query SELECT * FROM t WHERE a = 99; can't use the plan patch my_hint3, because the literal value "99" doesn't match "88".

When you use parameter-sensitive plan patches, consider the following:

Parameter-sensitive plan patches don't support a mixture of literal and constant values and parameter markers in the query text.
When you create a parameter-sensitive plan patch and a default plan patch for the same query, the parameter-sensitive plan patch is preferred over the default patch.
If you want to use the query ID to create a parameter-sensitive plan patch, make sure that the query executed in the current session. The parameter values from the most recent execution (in the current session) are used to create the plan patch.

Verify the application of the query plan patch

After you create the plan patch, use the following methods to verify that the query plan is forced accordingly.

Use the EXPLAIN command or the EXPLAIN (ANALYZE) command.

To view the hints that the planner is trying to apply, you can set the following flags at the session level before you run the EXPLAIN command:
```
SET pg_hint_plan.debug_print = ON;
SET client_min_messages = LOG;
```
Use the auto_explain extension.

Manage query plan patches

AlloyDB lets you view, enable and disable, and delete a query plan patch.

View a query plan patch

To view existing plan patches, use the google_query_plan_patch_view function, as shown in the following example:

postgres=>\x
postgres=>select * from google_query_plan_patch_view limit 1;
-[ RECORD 1 ]-----+-----------------------------
plan_patch_name | my_hint1
sql_id | -6875839275481643436
id | 9
query_string | SELECT * FROM t WHERE a = ?;
application_name |
hints | IndexScan(t)
disabled | f

Enable a query plan patch

To enable an existing plan patch, use the google_enable_plan_patch(PLAN_PATCH_NAME) function. By default, a plan patch is enabled when you create it.

For example, to re-enable the previously disabled plan patch my_hint1 from the database, run the following function:

SELECT google_enable_plan_patch('my_hint1');

Disable a query plan patch

To disable an existing plan patch, use the google_disable_plan_patch(PLAN_PATCH_NAME) function.

For example, to delete the example plan patch my_hint1 from the database, run the following function:

SELECT google_disable_plan_patch('my_hint1');

Delete a query plan patch

To delete a plan patch, use the google_delete_plan_patch(PLAN_PATCH_NAME) function.