sql-optimization

Start by running EXPLAIN ANALYZE to see the actual execution plan and identify sequential scans or bad row estimates. Then add indexes on WHERE clause columns, rewrite anti-patterns like SELECT * or OR conditions, and run ANALYZE to update table statistics before measuring again.

Use select_related for foreign key relationships or prefetch_related for many-to-many relations to fetch related data in a single JOIN query. This reduces 1001 queries down to 1, eliminating per-row roundtrips to the database.

Place equality condition columns first, range conditions second, and ORDER BY columns last, with the most selective column leading. For example, index (user_id, status, created_at DESC) for a query filtering by user and status then sorting by date.

Common causes include applying functions to indexed columns like LOWER(email), leading wildcards in LIKE patterns, or stale table statistics. Fix these with functional or trigram indexes, full-text search, or running ANALYZE to refresh planner statistics.

Use materialized views for expensive aggregations queried repeatedly, such as daily revenue rollups over millions of rows. They precompute results for instant reads and can be refreshed periodically with REFRESH MATERIALIZED VIEW CONCURRENTLY.

Partition tables exceeding roughly 100 million rows, typically by date range, so queries only scan relevant partitions instead of the entire table. Each partition gets its own indexes, which also keeps index sizes smaller and maintenance faster.