# SQL performance (dashboard widgets)

A dashboard tab runs a few widget queries at a time (**4 per process**,
8 across both HTTP workers). Each query is
killed by Postgres at **~25s** (`statement_timeout`). When a query finishes,
its slot is freed and the next queued widget starts — the queue is FIFO.
Timeouts you see as `canceling statement due to statement timeout` mean the
SQL **held a slot** and was too slow, not that the queue was stuck.

Write SQL that finishes in a few seconds **with the operator's filters
applied** (date range, sede, persona, etc.). `Sede = Todas` skipping a
filter is not a substitute for a plan that works when they pick a real sede.

## Date predicates

Prefer a native `date` column, or a half-open timestamp range:

```sql
AND m.date >= {{fecha_inicio}}
AND m.date <= {{fecha_final}}

-- or
AND m.message_date >= {{fecha_inicio}}::timestamptz
AND m.message_date < ({{fecha_final}}::date + 1)
```

Never wrap a timestamp: `m.message_date::date` / `CAST(m.message_date AS DATE)`
prevents a btree index and forces a heap filter of every tenant row.

## Dimension filters (sede, persona, tags)

Build the small set **once**, then join or `IN`:

```sql
AND (
  'Todas' IN {{sede}}
  OR m.contact_id IN (
    SELECT tas.target_id
    FROM frepi_tags.tag_assignment tas
    JOIN frepi_tags.tag ts
      ON ts.id = tas.tag_id AND ts.tenant_id = tas.tenant_id
    WHERE tas.tenant_id = m.tenant_id
      AND tas.target_type = 'contact'
      AND ts.name IN {{sede}}
  )
)
```

Never a **correlated** `EXISTS (… WHERE tas.target_id = m.contact_id)` on a
fact table. That becomes a nested loop per message (tens of thousands of
index lookups) and is what timed out a site-filtered operations
dashboard.

`'Todas' IN {{param}} OR <filter>` is the right way to no-op an "all" enum.

## Related KPIs — one query, not eight

Counts that share the same FROM/WHERE (enviados, entregados, errores, %)
belong in **one** saved query:

```sql
SELECT
  COUNT(*)::bigint AS enviados,
  COUNT(*) FILTER (WHERE status IN ('channel_delivered', 'channel_read'))::bigint AS efectivos,
  COUNT(*) FILTER (WHERE status IN ('error', 'channel_error'))::bigint AS con_error
FROM public_v2.conversation_message m
WHERE …
```

That uses **one slot** and one scan. Eight `SELECT COUNT(*)` widgets over the
same table stampede the 2-vCPU pipeline DB and make each query ~5× slower.

## Phone calls

Phone-call transcripts live in `conversation_message` too — they are NOT
WhatsApp rows. Filter with `channel = 'phone' AND kind = 'call'`; the
transcript is in `body` and the recording URL in `media_url`. Do not wrap
`body` in `unaccent()` (sequential scan), and do not conclude "no calls
exist" from a WhatsApp-only filter.

WhatsApp voice-note STT is **not** in `body` (caption stays there). Join
`public_marts.content_message_transcripts_v1` on `wamid`. If that mart is
missing, the workspace has not applied the transcript exposure migration.

## Other

- `COUNT(DISTINCT contact_id)`: filter the grain first; distinct last.
- Do not wrap columns in functions in `WHERE` (`CAST`, `LOWER` on a join key).
- If `query` / `bi_execute_sql` times out, rewrite **before** `save_query`.
  A saved timeout becomes a red KPI card on every dashboard refresh.