query-optimization-lab

name	query-optimization-lab
description	Optimize database queries across this codebase using a local-first benchmark loop. Use when a query is slow, memory-heavy, timing out, causing socket hang ups, scanning too many rows, or when improving ClickHouse/Postgres throughput, joins, aggregates, lookup tables, pipeline concurrency, or benchmark scripts.

Query Optimization Lab

Use this skill for any query-performance work in this repo.

IMPORTANT — LOCAL ONLY: ALL benchmarking and query testing MUST run against local ClickHouse (http://localhost:8123), never production. Start local ClickHouse with npm run clickhouse:up before doing anything else. Production credentials may be provided for reading error logs or table schemas only — never run benchmark queries, INSERT experiments, or TRUNCATE against production. If local ClickHouse is not running, start it first. If it lacks the needed data, seed it using an existing or new benchmark script.

Core Loop

Follow this loop exactly:

1. Identify the exact hot query or query family (you may read prod system.query_log / pipeline_errors to find it).
2. Start local ClickHouse (npm run clickhouse:up) and reproduce the query locally.
3. Benchmark the current shape on local ClickHouse.
4. Make one targeted query-shape change.
5. Benchmark again on local ClickHouse on the same dataset.
6. Keep the change only if the important metrics improve without breaking correctness.
7. Only after the query is lighter, revisit concurrency.

Optimization Principles

• Prefer probe-then-hydrate: First collect a small id set, then load wide rows once.
• Remove duplicate aggregation: Do not repeat argMax(... ) GROUP BY ... scans when one hydrate pass can serve multiple signals.
• Scope joins before joining: Join the smallest possible intermediate set, not the full lookup table.
• Know which setting covers which operation: max_bytes_before_external_group_by only spills GROUP BY to disk — it does NOT help JOINs. For JOIN hash table OOMs (FillingRightJoinSide), use join_algorithm='auto' + max_bytes_in_join. max_memory_usage is a hard kill limit, not a spill trigger.
• Benchmark the actual pain: Measure read_rows, read_bytes, memory_usage, elapsed_ns, and result_rows.
• Change one thing at a time: A benchmark must isolate the hypothesis.
• Keep a baseline: Compare before/after on the same dataset and same parameters.
• Favor objective keep/discard: Keep changes that improve the measured bottleneck. Revert changes that only “feel cleaner”.

Repo Defaults

• Local ClickHouse: npm run clickhouse:up
• ClickHouse URL: http://localhost:8123
• Main guide: docs/local-clickhouse-query-benchmarking.md
• Existing benchmark scripts:
  • apps/noticed-agent/scripts/benchmark-linkedin-match-queries.ts
  • apps/noticed-agent/scripts/benchmark-priority-enrichment-queries.ts
  • apps/noticed-agent/scripts/benchmark-query-optimization-suite.ts

Required Workflow

1. Find the Hot Path

• If prod credentials are available, query system.query_log to find the exact failing SQL:

  SELECT query, exception, memory_usage, read_rows, event_time
  FROM system.query_log
  WHERE type IN ('ExceptionWhileProcessing', 'ExceptionBeforeStart')
    AND event_time >= now() - INTERVAL 1 DAY
  ORDER BY event_time DESC LIMIT 20
  

  Also check pipeline_errors for application-level error patterns:

  SELECT topic, error_type, count() as cnt, max(created_at) as last_seen
  FROM pipeline_errors WHERE created_at >= now() - INTERVAL 7 DAY
  GROUP BY topic, error_type ORDER BY cnt DESC
  

  These are READ-ONLY queries against prod — never run mutations or benchmarks there.
• Read the production query code in the repo. Find the exact SQL shape, not a paraphrase.
• Find every query in the path if the hot path fans out into multiple queries.
• Identify whether the real problem is:
  • full-table scan
  • repeated aggregation
  • wide join / hash build (FillingRightJoinSide in error = JOIN hash OOM)
  • FINAL
  • anti-join / NOT IN
  • expensive text match
  • concurrency multiplying a moderately expensive query

2. Build or Reuse a Local Benchmark

• Reuse an existing benchmark script when possible.
• If none exists, create one under apps/noticed-agent/scripts/benchmark-<area>-queries.ts.
• Seed either:
  • production-like local data, or
  • synthetic data with the right cardinality, skew, and duplication
• Use HTTP query execution and capture X-ClickHouse-Summary.

3. Compare Before vs After

Always use the benchmark suite script — never ad-hoc curl queries. The suite generates CSV reports under apps/noticed-agent/scripts/benchmark-reports/ that serve as the permanent record.

1. Run the full suite BEFORE making changes to capture the baseline CSV:

   CLICKHOUSE_URL=http://localhost:8123 npx tsx apps/noticed-agent/scripts/benchmark-query-optimization-suite.ts all
   

2. Make the change.
3. Run the suite AFTER to capture the comparison CSV:

   CLICKHOUSE_URL=http://localhost:8123 npx tsx apps/noticed-agent/scripts/benchmark-query-optimization-suite.ts suite
   

   (Use suite to skip re-seeding since data is unchanged.)

Other suite commands for targeted work:

• individual — Phase 1 only (per-family micro-benchmarks)
• workload — Phase 2 only (combined concurrent workload)
• registry — Phase 3 only (all registered production SELECTs)

The suite CSV captures read_rows, read_bytes, memory_usage, elapsed_ns, and result_rows for every query.

The suite is mandatory for shared query-shape changes:

• Phase 1: one benchmark per hot query family
• Phase 2: run the combined workload benchmark and the small concurrent workload test
• Keep local wins only if the family benchmark improves.
• Keep global changes only if they still improve the combined suite.
• Commit both the baseline and after CSV files alongside your code changes.

4. Promote Carefully

• If the query gets materially lighter, then adjust worker concurrency.
• Increase throughput in steps, not all at once.
• If retries, socket hang ups, or OOMs remain, go back to query shape before further concurrency increases.

Keep/Discard Rules

Keep the change if at least one bottleneck metric materially improves and correctness is preserved.

Discard or revise the change if:

• result_rows meaningfully changes without justification
• memory drops but latency explodes
• latency improves but scan size stays huge and will still collapse under concurrency
• the change only helps the synthetic case and not the actual hot query

Autoresearch Principles

Use these ideas while optimizing:

• Treat the benchmark as the judge, not intuition.
• Make small, reversible experiments.
• Use a fixed dataset and fixed evaluation loop.
• Log each experiment in plain language: hypothesis, change, metrics, keep/discard.
• Evolve the optimization playbook over time: if a pattern works repeatedly, add it to the benchmark docs or a shared helper.

Output Format

When doing query optimization, report results in this shape:

## Query
<what path/query was optimized>

## Hypothesis
<one sentence>

## Change
<what changed in query shape>

## Benchmark
- before: ...
- after: ...
- delta: ...

## Decision
keep | discard

## Next Step
<optional>

Additional References

• For the full workflow and metrics, read WORKFLOW.md
• For common query rewrite patterns, read PATTERNS.md
• For experiment logging and keep/discard discipline, read EXPERIMENTS.md