Query Optimizer

query-optimizer

Make the query fast based on evidence, not guesses.

Process

1. Get the plan: run EXPLAIN (ANALYZE, BUFFERS) . Never optimize without it.
2. Read the plan top-down: find the costliest node. Look for:

• Seq Scan on a big table where an index could be used.
• Rows Removed by Filter high → missing/!selective index.
• Estimated vs actual rows far apart → stale stats (ANALYZE).
• Nested loop over many rows → maybe a hash/merge join is better.

1. Propose the smallest fix: an index, a rewritten predicate, a LIMIT, or pagination.
2. Re-measure with EXPLAIN ANALYZE and report before/after timing. Don't claim a win without it.

Common fixes

• Composite index ordered by selectivity for multi-column filters.
• Partial index (where active) for queries that always filter the same way.
• Replace OFFSET pagination with keyset (where id > $last) for deep pages.
• Kill N+1: one query per row in app code → use a join or IN (...)/= ANY.

Output

• The plan's bottleneck in one sentence.
• The fix (SQL/DDL).
• Before/after timing from real EXPLAIN ANALYZE runs.

Guardrails

• An index isn't free — it slows writes. Only add what the plan justifies.
• Don't add an index that duplicates an existing one's prefix.

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: m-binimran
• Source: m-binimran/dev-pack
• License: MIT

Install and usage instructions live in the source repository linked above.