From noticed-labs
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.
How this skill is triggered — by the user, by Claude, or both
Slash command
/noticed-labs:query-optimization-labThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
Use this skill for any query-performance work in this repo.
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.
Follow this loop exactly:
system.query_log / pipeline_errors to find it).npm run clickhouse:up) and reproduce the query locally.id set, then load wide rows once.argMax(... ) GROUP BY ... scans when one hydrate pass can serve multiple signals.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.read_rows, read_bytes, memory_usage, elapsed_ns, and result_rows.npm run clickhouse:uphttp://localhost:8123docs/local-clickhouse-query-benchmarking.mdapps/noticed-agent/scripts/benchmark-linkedin-match-queries.tsapps/noticed-agent/scripts/benchmark-priority-enrichment-queries.tsapps/noticed-agent/scripts/benchmark-query-optimization-suite.tssystem.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.FillingRightJoinSide in error = JOIN hash OOM)FINALNOT INapps/noticed-agent/scripts/benchmark-<area>-queries.ts.X-ClickHouse-Summary.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.
CLICKHOUSE_URL=http://localhost:8123 npx tsx apps/noticed-agent/scripts/benchmark-query-optimization-suite.ts all
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:
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 justificationUse these ideas while optimizing:
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>
Provides UI/UX resources: 50+ styles, color palettes, font pairings, guidelines, charts for web/mobile across React, Next.js, Vue, Svelte, Tailwind, React Native, Flutter. Aids planning, building, reviewing interfaces.
Searches MemPalace before answering questions about past work, people, projects, or prior decisions. Returns verbatim stored content instead of guessing from model memory.
npx claudepluginhub noticedso/noticed-labs --plugin noticed-labs