What is your checklist for optimizing a slow SQL query?
What is your checklist for optimizing a slow SQL query?
Work from measurement to cause to fix. Guessing and adding random indexes is the common failure.
pg_stat_statements (PostgreSQL), Query Store (SQL Server) or AWR (Oracle) to rank queries by total time, not just worst single run.Run EXPLAIN (ANALYZE, BUFFERS) or the equivalent and look for large scans, wrong row estimates, spills and huge loop counts.
| Step | Check |
|---|---|
| Statistics | ANALYZE / UPDATE STATISTICS so estimates are right |
| Indexes | filter, join and sort columns indexed; composite order matches the query; covering where it pays |
| Sargability | no functions or type mismatches on indexed columns |
| Rows early | filter before joining and grouping; select only needed columns (SELECT * blocks covering indexes) |
| Join shape | join keys indexed and same type; remove unnecessary joins; replace correlated subqueries with joins or window functions |
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.