> ## Documentation Index
> Fetch the complete documentation index at: https://help.treble.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Query Optimization

> How to write fast, efficient queries against the Analytics Warehouse.

# Query Optimization

The Analytics Warehouse runs on ClickHouse, a columnar database. Queries behave differently than in a traditional relational database — a few habits make them dramatically faster.

## Key principles

### 1. Always filter by date

Data is physically organized by time. A date filter lets ClickHouse skip whole chunks of history without reading them — and since the warehouse now serves **full history** (not just 3 months), date filters matter more than ever.

Each table has one primary time column to filter on:

| Table                                                                                                                                                                               | Filter on      |
| ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------- |
| `fact_treble_sessions`, `fact_treble_session_messages`, `fact_treble_session_variables`, `fact_ad_sessions`                                                                         | `created_at`   |
| `fact_treble_session_nodes`                                                                                                                                                         | `entered_at`   |
| `fact_campaign_sends`                                                                                                                                                               | `scheduled_at` |
| `fact_campaign_daily`, `fact_agent_daily`                                                                                                                                           | `day`          |
| `fact_agent_conversations`, `fact_agent_conversation_messages`, `fact_agent_conversation_transfers`, `fact_agent_status_changes`, `fact_whatsapp_link_events`, `fact_target_events` | `created_at`   |
| `fact_hsm_responses`                                                                                                                                                                | `responded_at` |

```sql theme={null}
-- Good: reads only the relevant slice of history
SELECT count() FROM fact_treble_sessions
WHERE created_at >= '2026-07-01' AND created_at < '2026-08-01'

-- Bad: scans the full history of the table
SELECT count() FROM fact_treble_sessions
WHERE poll_name = 'Welcome flow'
```

### 2. You don't need to filter by company\_id

Your user has a **row policy** that filters by your company automatically, and the data is sorted by company first — every query benefits from this without you adding anything.

### 3. Select only the columns you need

ClickHouse reads only the columns you mention. This matters most on wide text columns (`text`, `content`, `ai_automator_instructions`): leaving them out of a `SELECT` can cut a query's cost by an order of magnitude.

```sql theme={null}
-- Good: 3 narrow columns
SELECT session_id, created_at, status
FROM fact_treble_sessions
WHERE created_at >= now() - INTERVAL 7 DAY

-- Avoid: SELECT * drags every wide column along
SELECT * FROM fact_treble_session_messages
WHERE created_at >= now() - INTERVAL 7 DAY
```

### 4. Aggregate in the database, not in your tool

Pull answers, not raw rows. A `GROUP BY` over millions of rows returns in well under a second; downloading those millions of rows into a BI tool doesn't.

### 5. Use the daily rollups when they fit

[`fact_campaign_daily`](/en/docs/data-warehouse-v2/fact-campaign-daily) and [`fact_agent_daily`](/en/docs/data-warehouse-v2/fact-agent-daily) pre-compute the most common dashboard metrics. A dashboard over a rollup reads thousands of rows instead of millions.

### 6. Use LIMIT when exploring

```sql theme={null}
SELECT * FROM fact_treble_session_messages
WHERE created_at >= now() - INTERVAL 1 DAY
ORDER BY created_at DESC
LIMIT 100
```

## JOIN tips

* **Join through ids, not names**: `session_id`, `agent_conversation_id`, `hsm_id` — names are for display.
* **Put the small table on the right side** of the JOIN — ClickHouse loads the right side into memory. Dimensions are always small; joining two big fact tables works best when both sides carry a date filter.
* **Include `company_id` in the join key** when joining facts to dimensions (as the examples in this documentation do).

```sql theme={null}
-- Sessions enriched with channel display name
SELECT s.poll_name, c.display_name, count() AS sessions
FROM fact_treble_sessions AS s
LEFT JOIN dim_channels AS c
    ON c.company_id = s.company_id AND c.channel_id = s.channel_id
WHERE s.created_at >= today() - 30
GROUP BY s.poll_name, c.display_name
```

## System limits

Your user runs with protection limits that keep the platform stable for everyone:

| Limit                          | Value      |
| ------------------------------ | ---------- |
| Maximum execution time         | 30 seconds |
| Maximum rows read              | 50 million |
| Maximum bytes read             | 5 GB       |
| Maximum rows in result         | 500,000    |
| Maximum memory per query       | 2 GB       |
| Maximum columns read per query | 50         |

If a query is cancelled by a limit:

* Add or tighten the date filter (the fix in almost every case)
* Drop unneeded columns — especially wide text columns
* Aggregate with `GROUP BY` instead of pulling raw rows
* Break very large extractions into month-sized chunks (see [Incremental Sync](/en/docs/data-warehouse-v2/incremental-sync) for the pattern that avoids large extractions entirely)