Skip to main content

Performance troubleshooting

When a report is slow, identify which loading status is taking the time, then jump to the matching symptom below. For warehouse-side investigation (slow query execution, high CPU, EXPLAIN ANALYZE), see Diagnose data warehouse issues at the bottom.

"My report takes too long to load"​

Try to narrow down to one of the more specific symptoms below (Pending, Running query, Processing result, Loading, or Fetching result).

"My report takes too long at Pending status"​

performance-perf-pending performance-perf-pending3

Causes​

Resolution​

  1. Validate the cause using Blocked Jobs Analysis of Job Performance Analytics
  2. Possible optimizations:

"My report takes too long at Running query… status"​

performance-perf-running

Causes​

Without Holistics Canal​

With Holistics Canal​

Notes​

  • How to validate the cause:
    • Compare Holistics Job Logs against your Data Warehouse logs (Holistics Job Logs are in Job Monitoring)
    • Compare against the execution/query plan on the Data Warehouse (e.g. using EXPLAIN ANALYZE) — see Run EXPLAIN ANALYZE on slow/suspected queries
    • Try downloading the data from the Data Warehouse console and time the download

"My query takes too long to run on the data warehouse"​

Causes​

  • The query is costly/complex and slow by itself
  • The Data Warehouse is overloaded
    • Increase resources (CPU, memory) on the Data Warehouse

For deeper warehouse-side diagnosis, see Diagnose data warehouse issues below.

"My report takes too long at Processing result status"​

performance-perf-process-result

Causes​

  • The query result is too big — slow to cache or render in Holistics

Resolution​

"My report takes too long at Loading status"​

performance-perf-loading

Possible causes:

  • Holistics is taking long on report pre-processing — usually because your modeling code is large or complex
  • The browser is slow to communicate with Holistics servers, typically because:

We continuously improve the Loading step. If it's still slow, contact [email protected] with:

  • The URL of the page
  • The Report or Dashboard title
  • The duration of the Loading status

"My report takes too long at Fetching result… status"​

performance-perf-fetching-result3
Note

This status only appears when Holistics Canal is enabled.

Causes​

  • The query result is too big and slow to fetch back to Holistics

Resolution​

"My users complain about performance but I don't know where to start"​

Use Job Performance Analytics.

Other / unmatched​

If your performance issue doesn't match any symptom above, or the suggested solutions don't work, contact Holistics Support with as much of the following as possible:

  • The URL of the page
  • The duration you experienced and the duration you expected
  • Screen recordings or screenshots
  • The steps that led to the issue
  • If a job is involved, follow Report slow-running jobs

Diagnose data warehouse issues​

This section covers warehouse-side investigation methods — long-running jobs, repeated failures, CPU usage, and query plans. Use it together with the symptom-based diagnosis above when the issue points back to your data warehouse.

Investigation methods​

Identify long-running jobs in Job Monitoring​

See Job Monitoring.

Check if there are many failed jobs with the same errors​

If multiple jobs fail in a short window, the cause is often systemic — usually in the data warehouse layer. Common patterns:

  • DB fails to write Persisted Models
  • DB out of memory
  • DB connection refused
  • DB connection timed out

To investigate:

  1. Open Job Monitoring Dashboard.

  2. Look for many jobs failing with the same error. The screenshots below show jobs failing because of issues on the data warehouse side.

Check if CPU utilization of your data warehouse is high​

info

Not every data warehouse offers CPU utilization monitoring. If yours doesn't, look up workarounds for your specific warehouse.

If CPU utilization is high, SQL queries take a long time to process. Commands like cancelling a job won't even run because the database is unresponsive.

Common causes of high CPU utilization:

  • High number of logical reads, due to:
    • Queries that lack indexes
    • Outdated index statistics
    • Inefficient queries
  • Increased workload

To investigate:

  1. Check the CPU utilization metric on your warehouse:
  2. If CPU is frequently abnormally high, list the long-running queries during peak periods:
  3. Send these queries to Holistics Support.

Run EXPLAIN ANALYZE on slow/suspected queries​

EXPLAIN ANALYZE reveals the query plan — useful for identifying which parts of a slow query take the most time.

General instructions​

  1. In Holistics, get the generated SQL of your slow job.

    Go to Job Monitoring and find the slow job's logs. Copy the generated SQL — Holistics-generated SQL has a /* - Job ID: xxxxxxxxxx* */ comment at the top.

  2. Append EXPLAIN ANALYZE and run it against your data warehouse.

    Paste the SQL into your DB engine, prepend EXPLAIN ANALYZE, and run. Send the output to Holistics if you're not sure how to interpret it.

    To visualize the query plan, paste it into a tool like explain.depesz.com.

Snowflake-specific instructions​

  1. Same as step 1 of General Instructions.

  2. Find the full query in the Snowflake UI:

    • Or re-run the SQL in Holistics Editor

    Then go to Snowflake's History page and look for the latest query.

  3. Get detailed query info.

    info

    When sending troubleshooting info to Holistics, include the Query Detail view and all Query Profile screenshots.

    Click the SQL to open the Query Detail view.

    Navigate to the Query Profile tab — there can be multiple profile tabs, check all of them.

References​

Mitigation methods​

Cancel long-running jobs in Holistics​

See Cancel long-running jobs.

Manually cancel jobs in your data warehouse​

For jobs that can't be cancelled via Job Monitoring (e.g. field-suggestion jobs), cancel them from the warehouse directly.

  1. Get the pid of the long-running process. Check the query column to confirm it's the right one:

    select * from pg_stat_activity
    where query_start < now() - interval '1 hour'

  2. Cancel the long-running process:

    select pg_cancel_backend(the_pid_collected_from_step_1)

Improvement methods​

Add table indexing/partitioning/clustering keys where relevant​

Indexes can dramatically improve query speed and reduce CPU usage on your warehouse.

  1. Add indexes where the query plan suggests they help:
  2. Run EXPLAIN ANALYZE again to confirm the indexes are used.

Adding indexes speeds up reads but slows inserts and uses extra disk. Consider trade-offs — see this overview.

Increase data warehouse computing resources​

Each data warehouse has its own scaling tools. A few examples:

Optimize your reports​

See Common levers on the Performance overview.

Open Markdown
Let us know what you think about this document :)