Database Query Optimization Strategies

Explain common database query optimization techniques.

Diagnostic Tools

Use EXPLAIN / EXPLAIN ANALYZE to analyze query execution plans and identify sequential scans and slow queries.

Index Optimization

• Add indexes for WHERE, JOIN ON, and ORDER BY columns.
• Order composite index columns by selectivity (highest cardinality first).
• Avoid too many indexes (they increase write costs).

Query Rewriting

• Avoid SELECT *; fetch only needed columns.
• Avoid applying functions to indexed columns in WHERE clauses (defeats index usage).
• Use JOINs instead of subqueries in most cases.
• Use keyset pagination instead of OFFSET for deep pagination (large OFFSET is slow).

N+1 Problem

Use ORM include/eager loading to prefetch related data, avoiding extra queries per record.

Connection Pooling

Use a connection pool (PgBouncer, HikariCP) to avoid frequent connection creation/teardown.