# Query Plan Pinning SingleStore now supports query plan pinning. Plan pinning ensures the optimizer will always pick a specific plan for a given query. Plan pinning allows users to select the most effective query plans for every query in their workload. Query plan pinning helps to: * Preventing query plan regression after automatic upgrades. * Avoiding changes to query plans after statistics change or global settings change that affect query plans. * Avoiding changes to query plans when a cluster is resized. ## Setting Up Query Plan Pins 1. Initial and configure storage for query plan pins Before plan pins can be used, storage must be initialized and configured for use. This process begins by running the command `BOOTSTRAP PLAN PIN DATABASE`, which requires root permissions. This creates a database `s2_plan_metadata` and then populates it with various stored procedures and tables. Please note that while this is a “user” database that can be managed like any other SingleStore database, negative side effects can occur if its contents are modified directly by end users. ```sql BOOTSTRAP PLAN PIN DATABASE; ``` ```sql SHOW DATABASES; ``` ```output +--------------------+ | Database | +--------------------+ | cluster | | information_schema | | memsql | | s2_plan_metadata | +--------------------+ ``` 2. Create a link to interact with the plan pinning database The cluster (or multiple clusters) must be configured with credentials to connect using the `CREATE LINK` syntax, creating a new link with the name `cluster.plan_pinning_link`. Multiple SingleStore clusters can interact with the same plan pinning database and share metadata. ```sql CREATE LINK cluster.plan_pinning_link AS MYSQL CONFIG '{ "host": "","port":}' CREDENTIALS '{ "username":"","password":""}'; ``` 3. Enable plan pinning The local database that stores pins is created by setting the engine variable `enable_plan_pinning` to `ON`. If this engine variable remains in its default value of `OFF`, no plan pinning actions will take place. ```sql SET GLOBAL enable_plan_pinning = ON; ``` 4. Creating pins Pins are created by executing the `PIN` statement with the designated query text, either from the plancache for all queries, a specific plan\_id and query text, or for all existing and new queries in the plancache. The `PIN` syntax can also be used to overwrite existing pins on a given query by using the `OVERWRITE` argument. ```sql PIN [OVERWRITE] ; PIN [OVERWRITE] FROM PLANCACHE; PIN [OVERWRITE] ALL FROM PLANCACHE; PIN [OVERWRITE] [ACTIVATE|DEACTIVATE] FROM PLANCACHE FOR QUERY ; SET GLOBAL pin_all_plans = 'AUTO'; ``` 5. Using pins Setting the following engine variable to `AUTO` will make the optimizer use plan pins that have been created: ```sql SET SESSION use_plan_pins = AUTO; SET GLOBAL use_plan_pins = AUTO; ``` Individual query plan pins are activated by running the `ACTIVATE PIN` statement with the specific query text. Conversely, individual query plan pins can be deactivated by running the `DEACTIVATE PIN` statement with the specific query text. ```sql ACTIVATE PIN ; DEACTIVATE PIN ; ``` ## Pin-Matching Criteria While identical (parametrized) queries across identical tables are supported for the purposes of pinning, the pin matching checks are more sophisticated and allow for tables to be modified and even replaced while minimizing the loss of previously pinned plans. The criteria to ensure a pin match are as follows: * The parameterized query text of the original query must match. * The same column names from the same table must be referenced in the pinned query. * The types of these columns must remain consistent. * Shard keys and table types of reference tables should remain unchanged. * Relevant table features present during pin creation must still exist. Despite the complexity of these criteria, they ensure that many common management operations do not impede the use of work invested in plan pins. These operations include: * Dropping and recreating a table * Adding new columns to the table * Adding new indexes to a table However, certain SingleStore features are crucial for specific plans, and changes to these features may render pins unusable: * Projections - If a projection of a base table is used in the pinned plan, it must still exist when the pin is loaded for it to be used. * Unique keys - Removing a unique key, including the primary key, from a table will make previously existing pins using the table unusable. * Computed columns - While removing a computed column explicitly referenced in a query will prevent pin reuse, if a computed column is matched within the query and replaced in the pinned plan, the column becomes a relevant feature. ## Understanding Plan Pinning State ## Pins and the Plancache The `OPTIMIZER_NOTES` field in the `information_schema.PLANCACHE` view now contains information about how plan pinning was used when creating the plancache entry. These new fields are: * `pinned_plan` - set to 1 if a pin was written to the configured storage based on the compiled result * `used_pinned_plan` - set to 1 if a lookup was made to the plan pinning service and an activated pin was found matching the given query In addition, these plan features can be viewed (if any exist) in a `plan_features` JSON key. This is formatted as a list of JSON objects detailing the relevant feature. These objects are present under the `plan_features` key in the `OPTIMIZER_NOTES` field and have the following properties: * `table_name`: the table the feature applies to * `feature_type`: the type of feature * `identifier`: a type-specific means of identifying what the feature’s required value is * `hash`: a version of the identifier used internally For example, a query may require a unique key constraint exists. In the plan features JSON key, the `feature_type` key would display the “Unique Key” value and the `identifier` key would contain the column(s) of the key. Generally, a plan feature is a property of a table in the plan that is necessary for the correctness or execution of the pinned plan. This means that a table with a unique key constraint may not necessarily have that key included as a feature for all related plans; it will only be included if those plans require it to be runnable. ## Commands to View Pinning State To view the current pinning state, run any of the following commands: ```sql SHOW PIN FOR ALL; SHOW PIN FOR QUERY ; SHOW PIN FOR TABLE ; SHOW PIN FOR DATABASE ; ``` ## Engine Variables Used for Query Plan Pinning * `enable_plan_pinning` - The available values are `OFF` and `ON`. The default value is `OFF`.  If set to `OFF`, no plan pinning actions will take place. * `pin_all_plans` - The available values are `AUTO`, `OFF`, `ACTIVATED`, and `DEACTIVATED`. The default value is `OFF`. If set to `ACTIVATED`, all new plans are pinned in the activated state. If set to `DEACTIVATED`, all new plans are pinned in the deactivated state. If set to `OFF`, no plans are pinned automatically. `AUTO` is the same as `ACTIVATED`. * `plan_pin_read_timeout_ms` - This is used to enforce a maximum latency penalty that can be incurred when the server looks for a plan pin before proceeding with compiling a new plan. This behavior would become relevant if the cluster hosting the plan pins became severely overloaded or experienced networking issues. For commands that need to read pins by explicit user action; such as, `ACTIVATE PIN` and `DEACTIVATE PIN`, usual timeouts are used instead. * `plan_pin_write_timeout_ms` - This is used to enforce a maximum latency penalty that can be incurred when the server writes a plan pin during compilation. This is only relevant for the case when a new query is being compiled without the presence of a pin, and defines the worst-case additional latency when `pin_all_plans` is set to be enabled. For commands that need to write pins by explicit user action; such as `ACTIVATE PIN` and `DEACTIVATE PIN`, usual timeouts are used instead. * `use_plan_pins` - The available values are `AUTO`, `TRUE`, and `FALSE`. The default value is `OFF`.  When set to `TRUE`, if the plan\_pin\_id is not found in memory or on disk, the optimizer will search for the plan in the pinning database. If set to `FALSE`, the optimizer will not look in the plan\_pin\_id in the pinning database. `AUTO` is the same as `TRUE`. ## Examples Using a sample customer rewards table based on birth months and ages to illustrate how to set pins for various use cases. ```sql CREATE DATABASE test1; USE test1; ``` ```sql CREATE TABLE BD_Rewards ( ID INT PRIMARY KEY AUTO_INCREMENT, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, BirthMonth INT NOT NULL, BirthDay INT NOT NULL, Age INT, SORT KEY (Age) ); ``` The following queries provide a list of names whose birthdays fall in the same month and a list of names of people whose ages are within a certain range. ```sql SELECT FirstName FROM BD_Rewards WHERE BirthMonth = 1; ``` ```sql SELECT FirstName FROM BD_Rewards WHERE Age BETWEEN 18 and 25; ``` The query plans created by executing these queries can be pinning in the following methods. ## Pin a Plan by Query Text A plan can be pinned using the literal query text: ```sql PIN (SELECT FirstName FROM BD_Rewards WHERE BirthMonth = 1); ``` ```output +---------------------------------------------------------------------------------------+ | PIN | +---------------------------------------------------------------------------------------+ | ROOT | | GATHER: `BD_Rewards`.`FirstName` AS FirstName | | FILTER: (`BD_Rewards`.`BirthMonth` = @_0) | | TABLE: test1.BD_Rewards AS BD_Rewards, 3 INDICES: (PRIMARY, BirthMonth, __SHARDKEY, ) | +---------------------------------------------------------------------------------------+ ``` ## Pin a Plan by Plan ID First, obtain the plan id for the query you want to pin: ```sql SHOW PLANCACHE; ``` Then, pin the plan using the following command: ```sql PIN 24 FROM PLANCACHE; ``` ```output +---------------------------------------------------------------------------------------+ | PIN | +---------------------------------------------------------------------------------------+ | ROOT | | GATHER: `BD_Rewards`.`FirstName` AS FirstName | | FILTER: (between `BD_Rewards`.`Age` @_0 @_1) | | TABLE: test1.BD_Rewards AS BD_Rewards, 3 INDICES: (PRIMARY, BirthMonth, __SHARDKEY, ) | +---------------------------------------------------------------------------------------+ ``` ## Pin All Plans in the Current In-memory Plan Cache All plans in the plancache can be pinned with the following command: ```sql PIN ALL FROM PLANCACHE; ``` ```output Query OK, 0 rows affected, 42 warnings ``` If there are plans that are not pinnable or already pinned, a warning message will be displayed. Run the `SHOW WARNINGS` command to view them. ## Continuous PIN Storage The `PIN ALL FROM PLANCACHE` command will apply only to existing queries. To set up your cluster to pin all plans continuously (including new queries), set the `pin_all_plans` engine variable to `AUTO`: ```sql SET GLOBAL pin_all_plans = 'AUTO'; ``` ## Per Session PIN Enabling/Disabling If you want to enable/disable pin per current session, run the following commands respectively. The default value for `pin_all_plans` is `OFF`: ```sql SET SESSION pin_all_plans = AUTO; ``` ```sql SET SESSION pin_all_plans = OFF; ``` ## Activate PIN on Query If you need to load a pin upon running a query, while also needing to disregard a deactivated pin you can activate pins on individual queries: ```sql ACTIVATE PIN SELECT FirstName, LastName FROM BD_Rewards WHERE Age >= 65; ``` ## Deactivate PIN on Query If your cluster is set up for all queries in your plancache to be pinned but you want to deactivate pins on an individual query, you can deactivate pins on individual queries: ```sql DEACTIVATE PIN SELECT FirstName, LastName FROM BD_Rewards WHERE Age <= 20; ``` ## Drop PINS from Query, Table, Database, or All If you want to drop pins and not just deactivate them, the following commands can be run based if you want to drop by table, database, or all pins on your cluster: ```sql DROP PIN FOR QUERY SELECT FirstName FROM BD_Rewards WHERE BirthMonth = 1; ``` ```sql DROP PIN FOR TABLE BD_Rewards; ``` ```sql DROP PIN FOR DATABASE test1; ``` ```sql DROP PIN FOR ALL; ``` These commands will not return how many pins were dropped. *** Modified at: July 12, 2024 Source: [/db/v9.1/query-data/query-plan-pinning/](https://docs.singlestore.com/db/v9.1/query-data/query-plan-pinning/) (An index of the documentation is available at /llms.txt)