Debugging Slow Queries (Using Postgres EXPLAIN)

Transcript

1. Debugging Slow Queries Using PostgreSQL’s EXPLAIN
2. Query Planner The query planner uses statistics on your data to choose: • Index(es) to use, if any • Order to JOIN tables • Most performant algorithms
3. EXPLAIN Shows us the execution plan for the query. • “EXPLAIN ANALYZE” - also runs the query to show actual costs. • Prepend to your query in psql (script/dbconsole) • Rails also has ActiveRecord::Relation#explain
 eg User.order(:created_at).limit(10).explain
4. Test your queries in PRODUCTION
5. An Example Seller feedbacks for a given user (paginated)
12. Why’s it slow? • No index (or non-optimal index) • Joins/Sorts causing large in-memory operations • Reading from disk instead of cache
13. Indexes Indexes make your queries fast
14. An analogy
19. What’s the right index? • An index is useful when relevant rows are clustered next to each other • Ideally, your index would cover every field you are filtering/sorting on • Practically, you want the index to limit the # of rows left to filter/sort to a reasonable amount (<100)
20. Multicolumn Indexes • Order of index fields == order of sort precedence
 (primary sort, secondary sort, …) • Queries must use the left most field(s) CREATE INDEX ON users (name, created_at)
21. CREATE INDEX ON users (name, created_at) CREATE INDEX ON users (created_at, name)
22. Multicolumn Indexes • Generally, fields filtered by range or sorted on go last • Otherwise choose an order that will be used by more queries
23. Which Index?
24. Which Index?
25. Which Index?
26. Which Index?
27. Which Index?
28. Which Index?
29. Reducing Rows Joins/sorts may be operating on large data sets even if end- results are small (eg due to LIMIT, DISTINCT)
30. Beyond Indexes • Experiment! • Involve fewer tables/joins • If filtering on a redundant column, there may be another way to filter • Consider splitting into 2 queries (or subquery, CTE)
 Especially useful with DISTINCT • Denormalize, as a last resort
31. Back to our example
37. Old New - No orders JOIN
40. Resources • Bug me! • http://use-the-index-luke.com/
 Comprehensive SQL performance guide by Marcus Winand • Also has a book: SQL Performance Explained • https://devcenter.heroku.com/articles/postgresql-indexes
 Lighter reading on indexes