{question}
How can I drop a query plan in SingleStore?
{question}
{answer}
If you recently have written a lot of data to a table, queries may benefit from new query plans. In SingleStore, query plans are optimized for your specific data distribution using automatically generated statistics about the data, including its cardinality and range.
Once a plan is generated, it will continue to be used unless you take manual action to drop its plan as described below, or it expires by not being used for 2 weeks. This ensures that if you have a plan with good performance, you can count on it not to change to a different plan automatically. In addition, as of SingleStore Version 7.3, no automatic process invalidates frequently used plans.
Note, we do expire unused plans in the background. For example, if they are not used for 12 hours, they are cleared out of memory and from the disk after 2 weeks. For more information, see our doc on plan expiration.
Analyze the Table
Therefore, it is a good practice to run ANALYZE TABLE on your tables after large writes. This will manually collect stats at that moment, so they are completely up to date. If the data in your tables changed by a factor of 2, this would invalidate all the plans that operate on this table and give those queries a chance to generate new plans based on current table stats the next time the query runs.
Drop All Plans Manually
However, ANALYZE only affects plans if the table is changed by a factor of 2, meaning it doubled in size or reduced by half. In some cases, you might want to manually clear out a plan to allow it to regenerate, regardless of changes to the table. Alternatively, maybe Support recommended that you drop a plan.
You can use DROP [plan_id|all] FROM PLANCACHE for this. Note the two options; the option "all" will drop all the plans on this node. The option "plan_id" will drop only the plan you specify. The instructions below show how to specify it.
Drop One Plan Manually
1) Run the query
To drop a particular plan, first, run it to ensure that it is in the memory plancache. This ensures it has a plan id.
select
name,
acctbal
from customer c
join orders o
on c.custkey = o.custkey
where
totalprice > 1000;
If this query takes a long time to run, you can kill it and continue with the steps below. It does not need to complete; it only needs to be in the in-memory plancache, so it has a plan id.
2) Identify the plan ID
Locate the plan_id by querying the information schema.
Insert enough of the query text in the LIKE clause between the wildcard characters % to identify the query and confirm it in the result. Remember that whitespace matters, but parameters will get parametrized, like 1000 here, represented by "^" in the plan.
memsql> select
-> plan_id,
-> query_text
-> from
-> information_schema.plancache
-> where
-> query_text like "%totalprice%";
+---------+-------------------------------------------------------------------------------------------------------------+
| plan_id | query_text |
+---------+-------------------------------------------------------------------------------------------------------------+
| 258 | select c_name, c_acctbal from customer c join orders o on c.c_custkey = o.o_custkey where o_totalprice > @ |
+---------+-------------------------------------------------------------------------------------------------------------+
3) Drop the plan by ID
Once you have confirmed the query text, insert the plan_id in the statement to drop it.
memsql> drop 258 from plancache;
Query OK, 0 rows affected (0.00 sec)
4) Confirm
If desired, confirm it is no longer in the plancache.
memsql> select
-> plan_id,
-> query_text
-> from
-> information_schema.plancache
-> where
-> query_text like "%totalprice%";
Empty set (0.00 sec)
5) Warm the plancache
Finally, run the query twice. The first run is to generate a new plan for it. It will be slightly slower than normal because it is interpreted from an initial step in the plan generation. The second time, the query is executed from the previously compiled code and may have improved performance.
6) Compare performance
Compare the runtime of the query now to the runtime before you dropped the plan. Additionally, you can run query plan operations such as Profile or use the Visual Explain on the query to examine metrics per operator, including memory_usage, and explore the operator tree structure.
The tradeoff for Dropping a Plan
When a query plan is dropped, it must be regenerated the next time the query runs. This causes the plan to run with less performance initially. Additionally, CPU and memory are used to optimize and generate the query plan. If the entire plancache is dropped during an active workload, this can cause a spike in CPU and memory usage to recreate all the plans of that workload at the same time. Therefore, if you plan to drop the entire plancache, SingleStore Support recommends doing so during a low traffic time.
In some cases, you may consider warming the cache after dropping plans. Once the cache is cleared, you can run some of your most common queries on the SingleStore cluster one after the other. On the first run of each unique query shape, the plan will be generated. The next time that query is run during your normal workload, its plan is already compiled on disk to run fast at the normal expected performance.
Version Information
This article was written for SingleStore Version 7.3. Additional details on product enhancements, including updates to plan invalidation, can be viewed in the release notes.
{answer}