Skip to main content

​
EXPLAIN & index audit — large retrieval paths

Evidence that the hot read paths use indexes, never a Seq Scan on a large table on the bounded path, and apply filters + privacy BEFORE the LIMIT. Generated by apps/api/src/topics/explain-audit.integration.spec.ts (run under the integration config against a migrated Postgres seeded with ~4k topics and ~4k broker operations across 20 providers, ANALYZEd). Direct Postgres — no cache layer.

​
How to read this

At fixture scale (a few thousand rows) Postgres often prefers a Seq Scan because the table is tiny and a scan is genuinely cheaper than an index — that does not mean the index is missing. To audit that each index is valid and serves the query (the behavior that holds at 50k+ rows where the scan is no longer free), the index-usage checks run with SET LOCAL enable_seqscan = off and confirm the planner switches to the index. The structural checks (filter/privacy before LIMIT) hold regardless of scale.

​
Indexes backing the paths

PathIndexMigration
Topic FTS searchGIN on search_tsv (weighted tsvector)0089
Topic list / match-files(workspace_id, slug) + scan filter on status/privacybase + 0089
Collection records keyset, doctorcollection perf composites0090
Broker per-connection list / invoke lookup(workspace_id, provider)0091
Broker cross-provider SEARCH(workspace_id, active, name) + pg_trgm GIN on lower(name)/lower(description)0092 (this round)
The broker SEARCH index (0092) is the gap this card closed: the cross-provider intent search ILIKEs name/description with a leading wildcard (%query%), which a B-tree cannot serve — the trigram GIN makes it index-assisted instead of a row-by-row filter over every operation in the workspace at scale.

​
Verdicts (captured plans)

Broker per-provider list — clean index path: 
Bitmap Index Scan on idx_broker_op_ws_provider
  Index Cond: ((workspace_id = $1) AND (provider = 'prov3'))
Broker cross-provider search (seqscan off) — served by an index, no Seq Scan; the (workspace_id, active, ILIKE) filter is in the WHERE, beneath the LIMIT: 
Limit
  -> Index Scan ... on broker_operations
       Filter: (active AND (lower(name) ~~ '%…%') OR lower(description) ~~ '%…%')
Topic FTS search (seqscan off) — index path, no Seq Scan; search_tsv @@ plainto_tsquery(...) and status <> 'archived' filter on the scan node, under the LIMIT. Topic list — privacy before LIMIT (the load-bearing invariant): the draft- privacy predicate (status <> 'draft' OR is_private IS NOT TRUE OR created_by IS NULL OR created_by = :principal) is a WHERE clause on the scan/Filter node, which EXPLAIN prints beneath the Limit node — proof it is evaluated before the bound (no privacy-after-limit leak). 686 of 4000 rows removed by the filter before the 40-row LIMIT in the captured run.

​
Acceptance

  • Filters + privacy run before LIMIT on every audited path ✓ (structural, EXPLAIN-confirmed).
  • Keyset/cursor paths use composite indexes; no deep-offset scan is required for the bounded reads ✓.
  • The one missing index (broker cross-provider search) was added via an explicit TypeORM migration (0092), never an ad-hoc CREATE INDEX ✓.
  • Reproducible: pnpm --filter @driftless/api test:integration (with a Postgres) runs the audit; the two card-validate specs (retrieve-eval.integration, collections-perf.service.integration) gate the correctness of these paths.