2. Investigating Expensive Queries

On this page

  1. If a query has a high run count that is using a lot 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's run?

  2. If you have identified a query with a low run count using a lot of resources, or a frequent query that is a normal occurrence, copy the query out of SingleSoreDB Studio. To get the query text from what’s displayed, query the information_schema table MV_QUERIES.

    SELECT * FROM information_schema.MV_QUERIES where activity_name = <identified_activity_name>;
  3. Check the PLAN_WARNINGS column for any hints about potential optimizations. If there are no hints, move onto the next step.

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

  5. Navigate to the Studio SQL Editor, and paste the query in.

  6. Run the query once in the editor by clicking the Run button in the top right hand corner. After the first run is complete, highlight the query and click 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. See an example of a visual PROFILE below:

  7. Look to see if there is anything unusual about the query plan. You may find a query plan operation that takes a lot of time. If so, investigate that particular operator. You may also that find that each component is similarly time consuming. For either case, here are some questions to consider:

    • Are you reshuffling or repartitioning a lot 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 doesn’t 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.

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

Next Steps

  • If you didn’t identify any further useful information here: move onto Step 3.

Last modified: March 8, 2024

Was this article helpful?