What is EXPLAIN Query Plan?
The command prefix showing how the database will execute a query: scan types, index usage, join order, and row estimates. Reading plans separates “add an index” guesses from evidence about what the planner actually does.
Example
A BYOB Supabase project serves chat history through RLS policies; EXPLAIN shows a sequential scan on messages, so the team adds a conversation-scoped index and re-checks until the plan shows an index scan.
What people get wrong
Adding indexes blindly for every slow query. Unused indexes slow every write and bloat backups; measure with EXPLAIN first, then index deliberately.
Related terms
B-Tree Index
The default balanced-tree structure making equality and range lookups fast without scanning every row. Add one where queries filter, join, or sort — then confirm with EXPLAIN before assuming victory.
N+1 Query Problem
Fetching a list with one query then one extra query per row for its relations — 1 plus N round trips. ORMs make it easy to write; batching, joins, or dataloaders collapse it to a constant handful.
Drizzle ORM
A typed TypeScript layer over SQL for schema definition and queries. Types generated from the live schema keep app code and database in agreement.
Composite Index
One index spanning several columns for queries that always filter them together, ordered most-selective first. A three-column composite beats three single-column indexes on the same query shape.
Partial Index
An index covering only rows matching a predicate, such as live subscriptions where status is active. Smaller, faster, and cheaper to maintain than indexing rows queries never touch.
Unique Constraint vs Index
A uniqueness guarantee enforced by the database versus a lookup accelerator with no such promise. Postgres implements unique constraints with an index, but only the constraint rejects duplicates.