writing-performant-querieslisted

# Writing Performant Queries ## Overview A Postgres query is binary: it either finds rows fast through an index (btree, ~`log(n)`) or it sequential-scans every row in the table. Your job is to make sure the hot queries hit an index — and to know when a seq scan is actually fine. ## When to Use - A query is slow or `EXPLAIN` shows `Seq Scan` on a large table. - Writing a SELECT/JOIN with WHERE filters and ORDER BY. - Deciding which index to add. - The planner ignores an index you expected it to use. ## Quick Reference | Situation | Rule | |---|---| | Fast single-row lookup | Needs an index: explicit index, unique constraint, or primary key | | Seq scan on <20k rows | Fine — effectively instant, don't index it | | Inner join condition | Join on primary keys; treat `ON` like `WHERE` for indexing | | Filter + sort | Compound index: filter cols first, ORDER BY cols last, aligned to sort dir | | Inspect a plan | `EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) <query>` | ## Compound Index Pattern Put filter columns first, the `ORDER BY` column last, matching the sort direction: ```sql -- query: WHERE status = $1 ORDER BY created_at DESC CREATE INDEX idx_tasks_status_created ON tasks (status, created_at DESC); ``` Postgres scans btrees in both directions, so `DESC` is often not strictly required — but writing it to match the query is good practice for compound indexes. ## Reading the Plan - `EXPLAIN ANALYZE` **executes** the query to get real timings — be careful