sql-query-optimizer

Run EXPLAIN ANALYZE on the query to identify sequential scans and expensive operations, then add indexes on filtered or joined columns and rewrite the query to fetch only needed columns. Benchmark before and after to measure improvement.

Look for Seq Scan nodes on large tables, high row counts removed by filters, and costly sort or hash operations. These indicate missing indexes or inefficient joins that should be addressed first.

EXISTS is generally faster because it short-circuits on the first matching row, while IN evaluates the full subquery. On large datasets, rewriting IN as EXISTS can be roughly three times faster.

Indexes are skipped when functions wrap the indexed column, such as LOWER(email), or when the query filter does not match the leading columns of a composite index. Use a functional index or reorder composite index columns to match the query pattern.

Enable slow query logging with log_min_duration_statement and query pg_stat_statements for statements with high mean execution time. Sort by mean_exec_time to prioritize the worst offenders.

A covering index includes all columns a query needs, created with the INCLUDE clause, enabling index-only scans without touching the table. Use it for frequent queries that read a small, fixed set of columns.