Sign inTry Free

DROP … FROM PLANCACHE

On this page

Forces a plan to be optimized and generated from scratch the next time it is run.

Syntax

DROP <plan_id> FROM PLANCACHE ON NODE <node_id>


DROP ALL FROM PLANCACHE


DROP PLAN FROM PLANCACHE [ON AGGREGATORS] FOR QUERY <query_text>

Arguments

  • <plan_id>: The ID of the query plan to remove. Find the plan_id by querying the MV_PLANCACHE view.

  • <node_id>: The ID of the node from which you wish to remove the plan.

Remarks

  • The DROP <plan_id> FROM PLANCACHE ON NODE <node_id> command will drop the plan only from the plancache on the node with ID <node_id>.

    • The plan will not be dropped from plancaches on other nodes. Dropping plancache entries on an aggregator node will not drop the corresponding leaf-only plans from the leaf plan caches.

  • The DROP ALL FROM PLANCACHE syntax can be used to drop all plans from the plancache. This command will only drop plans stored on the node where it is run.

  • The DROP PLAN FROM PLANCACHE [ON AGGREGATORS] FOR QUERY <query_text> syntax can be used to drop plans for specific queries.

    • If ON AGGREGATORS is not specified this command will drop the plan on the node on which it is run.

    • If ON AGGREGATORS is specified, the plan will be dropped on all aggregator nodes.

    • Selection queries only are supported in <query_text>.

  • Avoid running this command frequently since all queries are recompiled, resulting in a temporary decrease in workload performance.

  • Node Requirements for SingleStore Commands

Example

DROP 123 FROM PLANCACHE ON NODE 2;
DROP ALL FROM PLANCACHE;
DROP PLAN FROM PLANCACHE FOR QUERY SELECT * FROM testtable;
DROP PLAN FROM PLANCACHE ON AGGREGATORS FOR QUERY SELECT * FROM testtable;

Use Cases

Example 1: DBA app tuning

A DBA wants to create some new indexes and performs the following actions (not necessarily in the specified order):

  • Runs ANALYZE TABLE to invalidate all plans for the table based on the statistics that are significantly different from the current statistics but does not invalidate all other plans. However, ANALYZE TABLE requires a minimum threshold of changes in the table statistics to invalidate the existing plans for the table. Therefore, ANALYZE TABLE may not invalidate the plans unless the minimum threshold is reached. Hence, there is still a need to generate a new plan.

  • Runs the DROP FROM PLANCACHE command to ensure that plans are generated with the latest statistics and all the previously compiled query plans are invalidated and purged, both in-memory and on-disk. This command updates the stats and changes the indexes. It can be run either before or after ANALYZE TABLE.

The impact of recent index and stats changes are reflected in all the plans for all the queries, across the cluster.

Example 2: Testing first-run performance

By default, the the first time SingleStore encounters a particular query shape, it will compile the query asynchronously in the background for use in later invocations of that query. In the default mode, SingleStore interprets and compiles a query shape in parallel; the query runs in interpreted mode until compilation of the query shape completes. This speeds up the query execution time for long and complex queries, while at the same time providing efficient query plans for later use. Refer to Code Generation for more information on query compilation and query compilation options.

To test compilation and measure the first run performance accurately, drop plans from the plancache on on all nodes (aggregators and leaves) across the cluster, and then run the query to obtain first-run performance execution time.

Example 3: Reduce memory used by plancache

A user may wish to periodically drop plans to free up memory. Refer to Managing Plancache Memory and Disk Usage for information about managing plancache memory use.

Related Topics

Last modified: January 17, 2025

Was this article helpful?

On this page

Was this article helpful?