~/posts/databases/sql-query-optimisation-basics.md
databases

SQL Query Optimisation: The Basics That Actually Matter

EXPLAIN ANALYZE, index selectivity, avoiding full-table scans, the N+1 problem — the practical optimisation moves that produce real gains.

6 min read by admin indexing performance postgresql query-optimization sql
~/posts/databases/sql-query-optimisation-basics.md $ cat sql-query-optimisation-basics.md

Read the Execution Plan

Before optimising anything, run EXPLAIN ANALYZE. Look for Seq Scan on large tables — those are your targets. An index scan on 10M rows costs milliseconds; a sequential scan costs seconds.

Index Selectivity

An index on a boolean column with 50/50 distribution is useless — the planner will skip it and scan anyway. Indexes shine on high-cardinality columns: UUIDs, emails, timestamps.

The N+1 Problem

Fetching a list of 100 users then loading each user's posts with a separate query = 101 queries. Use a JOIN, or if you're using an ORM, eager load. N+1 is the most common source of slow APIs.