2. Investigating Expensive Queries

If a query has a high run count and is using a large amount of resources (on average), it is important to identify why the query is running at such high concurrency.

• Is this normal for your application?

• Can you change how often it is run?

If you have identified a query with a low run count that uses a large amount of resources, or a frequent query that is a normal occurrence, copy the query from SingleSoreDB Studio. To get the query text from what is displayed, query the information_schema table MV_QUERIES.

SELECT * FROM information_schema.MV_QUERIES where activity_name = <identified_activity_name>; 

Check the PLAN_WARNINGS column for any hints about potential optimizations. If there are no hints, continue to the next step.

If the query in question is an INSERT ... SELECT statement, copy only the SELECT clause of the query.

Navigate to the SQL Editor in the Studio for your database, and paste in the query.

Run the query in the SQL Editor by selecting the Run button in the top right-hand corner of the page. After the first run is complete, highlight the query and select the Visual Explain icon to the left of the Run button to display the query plan in a graphical interface. Here, you will be given a choice between EXPLAIN and PROFILE, which display the query plan and further details for each query operation, respectively. SingleStore recommends running both of these options.

Check if there is anything unusual about the query plan. You may find a query plan operation that takes a large amount of time. If so, investigate that particular operator. You may also find that each component is similarly time-consuming. In either case, here are some questions to consider:

• Are you reshuffling or repartitioning a large amount of data?

  1. Common indicators of this include the Project operator combined with large SELECT costs or Repartitioning being present in the query plan.

  2. If so, can you add or change a shard key?

• Are you doing a full scan of a table?

  1. Look for the TableScan operator to indicate this information.

  2. If so, can you add/change an index or sort key?

• Is the query performing slowly or using more resources because of a schema change?

• Is the query performing slowly because the data working set increased?

• Are autostats enabled for the tables in the query?

  1. Run SHOW CREATE TABLE

  2. Look for AUTOSTATS_ENABLED=FALSE, AUTOSTATS_CARDINALITY_MODE=OFF, or AUTOSTATS_HISTOGRAM_MODE in the CREATE TABLE statement to determine this.

• Do you need to invalidate and refresh your current query plan?

  1. Autostats do not invalidate the plan automatically. To invalidate the plan, run ANALYZE on the table manually.

  2. Alternatively, to drop the current plan, run DROP … FROM PLANCACHE and re-run the query to see if it performs better.

Refer to Optimizing Table Data Structures for additional details, as well as Query Tuning for further guidance on how to optimize specific query shapes and tables.