optimizing-sql

Start by running EXPLAIN ANALYZE to identify bottlenecks like sequential scans or expensive sorts. Then add indexes on filtered or joined columns, rewrite non-sargable conditions, and verify improvement by comparing before-and-after execution plans.

Focus on the scan type, cost range, estimated versus actual rows, and execution time. Sequential scans with high 'Rows Removed by Filter' indicate missing indexes, while large row estimate discrepancies suggest outdated table statistics.

Place equality filter columns first, ordered by selectivity, then range filter columns, and finally ORDER BY columns matching the sort direction. A composite index on (A, B, C) only helps queries that filter on the leading columns.

It covers PostgreSQL, MySQL, SQL Server, and Snowflake. Each database has dedicated guidance for its EXPLAIN format, index types such as GIN, full-text, clustered, and columnstore, and engine-specific features like Query Store or index hints.

Common causes include functions applied to the indexed column, implicit type conversion from mismatched data types, leading wildcards in LIKE patterns, and low column selectivity. Rewrite the condition into a sargable form or create an expression index.

Skip indexes on small tables under roughly 1,000 rows, low-selectivity columns like booleans, and write-heavy tables where each index adds 5-10% overhead to inserts and updates. Also avoid redundant indexes that overlap with existing composite indexes.