# Performance troubleshooting

> Symptom-based diagnosis for slow reports, plus a data-warehouse deep-dive.

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](#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"

### Causes
- Your Report [job queue](/docs/jobs/queues-and-workers) is overloaded
- And/or this user has reached their [Per-user Report Job Limit](/docs/jobs/controls#limit-number-of-report-jobs-run-per-user)

### Resolution
1. Validate the cause using [Blocked Jobs Analysis](https://community.holistics.io/t/closed-beta-job-performance-analytics-dashboard/1793/3) of Job Performance Analytics
2. Possible optimizations:
    - Optimize slow jobs (see "Running query…" below) to free up Report workers
    - [Disable Dashboard auto-run](/docs/dashboards/settings#dashboard-auto-run)
    - Increase the [Per-user Report Job Limit](/docs/jobs/controls#limit-number-of-report-jobs-run-per-user)
    - Request to [increase your Report Workers](/docs/jobs/controls#increase-your-default-slots-for-specific-job-queues)
    - Adjust [Unused Job Timeout](/docs/jobs/controls#automatically-cancel-unused-jobs)

## "My report takes too long at **Running query…** status"

### Causes

#### Without Holistics Canal
- The query execution is taking long on your Data Warehouse → see ["My query takes too long to run on the data warehouse"](#my-query-takes-too-long-to-run-on-the-data-warehouse) below
- And/or the query result is too big and slow to fetch back to Holistics. Try:
    - [Migrating to a Holistics Data Center](/docs/security-compliance/data-centers#migrating-to-another-region) physically closer to your Data Warehouse
    - [Reducing data points](/docs/performance#reduce-the-number-of-data-points-on-a-report)
    - [Enabling Holistics Canal](/docs/connect/settings/enable-holistics-canal)

#### With Holistics Canal
- The query execution is taking long on your Data Warehouse → see ["My query takes too long to run on the data warehouse"](#my-query-takes-too-long-to-run-on-the-data-warehouse) below

### Notes
- How to validate the cause:
    - Compare Holistics Job Logs against your Data Warehouse logs (Holistics Job Logs are in [Job Monitoring](/docs/monitoring/job-monitoring#monitoring-jobs))
    - Compare against the execution/query plan on the Data Warehouse (e.g. using `EXPLAIN ANALYZE`): see [Run EXPLAIN ANALYZE on slow/suspected queries](#run-explain-analyze-on-slowsuspected-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
    - Check the execution/query plan on the Data Warehouse (e.g. `EXPLAIN ANALYZE`): see [Run EXPLAIN ANALYZE on slow/suspected queries](#run-explain-analyze-on-slowsuspected-queries)
    - Possible optimizations:
      - Pre-aggregate data, manually or with [Aggregate Awareness](/docs/aggregate-awareness/)
      - Use [Query Parameters](/docs/query-parameters) to push down predicates and use database indexing more efficiently
      - Persist your query with [Model Persistence](/docs/query-models#model-persistence) (or database materialized views, dbt persistence, etc.)
      - Add indexes/clustering/partitioning on the Data Warehouse
- The Data Warehouse is overloaded
    - Increase resources (CPU, memory) on the Data Warehouse

For deeper warehouse-side diagnosis, see [Diagnose data warehouse issues](#diagnose-data-warehouse-issues) below.

## "My report takes too long at **Processing result** status"

### Causes
- The query result is too big: slow to cache or render in Holistics

### Resolution
- [Reduce data points](/docs/performance#reduce-the-number-of-data-points-on-a-report)
- [Enable Holistics Canal](/docs/connect/settings/enable-holistics-canal)

## "My report takes too long at **Loading** status"

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:
    - Your internet connection
    - You're physically distant from Holistics Data Center → consider [migrating to a closer Data Center](/docs/security-compliance/data-centers#migrating-to-another-region)
    - And/or your modeling code is large

We continuously improve the Loading step. If it's still slow, contact support@holistics.io 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"

:::info Note
This status only appears when [Holistics Canal is enabled](/docs/connect/settings/enable-holistics-canal).
:::

### Causes
- The query result is too big and slow to fetch back to Holistics

### Resolution
- [Migrate to a Holistics Data Center](/docs/security-compliance/data-centers#migrating-to-another-region) physically closer to your Data Warehouse
- [Reduce data points](/docs/performance#reduce-the-number-of-data-points-on-a-report)

## "My users complain about performance but I don't know where to start"
Use [Job Performance Analytics](/docs/monitoring/job-monitoring#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](/docs/jobs/report-slow-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](/docs/monitoring/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](/docs/monitoring/job-monitoring).
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](/docs/job-queue-optimization#cancel-running-jobs) 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:
    - [Use AWS CloudWatch to monitor CPU Utilization of AWS RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/monitoring-cloudwatch.html)
    - [Monitoring Warehouse Load in Snowflake](https://docs.snowflake.com/en/user-guide/warehouses-load-monitoring.html)
2. If CPU is frequently abnormally high, list the long-running queries during peak periods:
    - [Detect heavy processes in AWS RDS](https://aws.amazon.com/premiumsupport/knowledge-center/rds-instance-high-cpu/#:~:text=Using%20queries%20to%20detect%20the%20cause%20of%20CPU%20utilization%20in%20the%20workload)
    - [Use PostgreSQL pg_stat_activity to monitor long-running queries](https://aws.amazon.com/premiumsupport/knowledge-center/rds-instance-high-cpu/#:~:text=Using%20queries%20to%20detect%20the%20cause%20of%20CPU%20utilization%20in%20the%20workload)
    - [Identify spikes in Snowflake and use History to list out the heavy queries](https://docs.snowflake.com/en/user-guide/warehouses-load-monitoring.html#peak-query-performance)
3. Send these queries to Holistics Support.

#### Run EXPLAIN ANALYZE on slow/suspected queries {#run-explain-analyze-on-slowsuspected-queries}

`EXPLAIN ANALYZE` reveals the query plan, which is 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](/docs/monitoring/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](https://explain.depesz.com).

##### Snowflake-specific instructions

1. Same as step 1 of General Instructions.
2. Find the full query in the Snowflake UI:
    - Search the [History page](https://docs.snowflake.com/en/user-guide/ui-history.html) using SQL text parameters

    

    - 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
- [PostgreSQL](https://www.postgresql.org/docs/current/sql-explain.html)
- [BigQuery](https://cloud.google.com/bigquery/docs/query-plan-explanation)
- [Snowflake](https://docs.snowflake.com/en/sql-reference/sql/explain)
- [Redshift](https://docs.aws.amazon.com/redshift/latest/dg/r_EXPLAIN.html)
- [Athena](https://docs.aws.amazon.com/athena/latest/ug/athena-explain-statement.html)

### Mitigation methods

#### Cancel long-running jobs in Holistics
See [Cancel long-running jobs](/docs/job-queue-optimization#cancel-running-jobs).

#### Manually cancel jobs in your data warehouse

For jobs that can't be [cancelled via Job Monitoring](/docs/job-queue-optimization#cancel-running-jobs) (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:

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

2. Cancel the long-running process:

    ```sql
    select pg_cancel_backend(the_pid_collected_from_step_1)
    ```

### Improvement methods {#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](#run-explain-analyze-on-slowsuspected-queries) suggests they help:
   - [Add indexes in PostgreSQL](https://www.postgresql.org/docs/current/sql-createindex.html)
   - [Add clustering keys for Snowflake](https://docs.snowflake.com/en/user-guide/tables-clustering-keys.html#defining-a-clustering-key-for-a-table)
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](https://www.scaler.com/topics/sql/advantages-and-disadvantages-of-indexing-in-sql/#disadvantages-of-indexes-in-sql)).

#### Increase data warehouse computing resources
Each data warehouse has its own scaling tools. A few examples:
- **Redshift**:
    - [Resizing clusters](https://docs.aws.amazon.com/redshift/latest/mgmt/managing-cluster-operations.html#rs-resize-tutorial)
    - [Concurrency scaling](https://docs.aws.amazon.com/redshift/latest/mgmt/managing-cluster-operations.html#rs-resize-tutorial)

#### Optimize your reports
See [Common levers](/docs/performance#common-levers) on the Performance overview.