sql-optimization-patterns

Start by running EXPLAIN ANALYZE on the query to see the actual execution plan. Look for sequential scans on large tables, then add indexes on filtered or joined columns and rewrite the query to avoid functions in WHERE clauses that block index usage.

Focus on the scan type, cost, rows, and actual time fields. Sequential scans on large tables signal missing indexes, while large gaps between estimated and actual rows indicate stale statistics that ANALYZE can fix.

Replace per-record queries with a single JOIN or a batch query using WHERE id IN (...). In application code, load the parent records first, then fetch all related records in one query and group them by foreign key.

Use a composite index when queries filter or join on multiple columns together, with the most selective or equality column first. Column order matters because the index only supports leftmost-prefix lookups.

OFFSET forces the database to scan and discard all skipped rows, so page 5000 reads 100,000 rows. Cursor-based pagination using WHERE created_at < last_seen_value with a matching index stays fast at any depth.

Yes. Every index must be updated on INSERT, UPDATE, and DELETE, slowing writes and consuming storage. Monitor pg_stat_user_indexes for indexes with zero scans and drop unused ones.