Postgresql Explain

An brief tour of Postgresql Explain. Also goes into indexing strategies and query algorithms provided by the Postgresql database.

Transcript

1. Postgresql Explain Chennai.rb meet-up 19-Sep-2015
2. Postgresql Explain Abhishek Yadav @h6165
3. Postgresql Explain: Agenda • Relevance and justification • Syntax • Usage - SELECT • Index basics • Some Heuristics • Usage GROUP • Usage – JOIN • A bit about Mysql
4. Postgresql Explain: Relevance and Justification • Explain helps us understand the query execution plan – hence enabling us to speculate its performance and make amends • Explain provides some insights into the algorithms used.
5. Postgresql Explain: Syntax • EXPLAIN <query> • EXPLAIN ANALYSE <query> – BEGIN; EXPLAIN ANALYSE <query>; ROLLBACK • ActiveRecord::Relation#explain
6. Postgresql Explain: Usage • Simple SELECT query
7. Postgresql Explain: SELECT • SELECT with WHERE (no index)
8. Postgresql Explain: SELECT • SELECT with WHERE indexed
9. Postgresql Explain: SELECT • SELECT with WHERE indexed (bitmap)
10. Postgresql Explain: Index intro • On-disk data structure that helps locate data in tables • Write operations update the indexes • Mostly B-tree. Bitmap, Gin, Gist • Db chooses whether to use index, or which one • Cardinality matters
11. Postgresql Explain: Heuristics • Seq scan on large tables => An index can help • Seq scan on indexed tables => Wrongly indexed/ low-cardinality
12. Postgresql Explain: Group – Arbitrary example
13. Postgresql Explain: Join • LEFT JOIN – low cardinality – 100 posts, 50k comments
14. Postgresql Explain: Join • LEFT JOIN – better cardinality – 10k posts, 50k comments
15. Postgresql Explain: Join • INNER JOIN with GROUP and ORDER BY – No filters: 10k posts, 50k comments
16. Postgresql Explain: Join • INNER JOIN with GROUP and ORDER BY – With filters: 10k posts, 50k comments
17. Postgresql Explain: More Indexing Facts • Indexing JOIN predicates is useless • Filtering can help. • Join algorithm can change based on the table sizes: Nested or Hash (or Sort-Merge) • Index access can itself be inefficient • ORDER BY clause has extra cost. Can be reduced with indexes • GROUP BY improves with indexing • Partial indexes are available
18. Postgresql Explain: A little bit about Mysql • Mysql has a fairly basic EXPLAIN • Watch out for: Type column – 'Using index', 'eq_ref', 'const', 'primary' are fine – 'ref', 'range' may be okay – 'ALL' is not fine • 'Rows' column is also a good indicator
19. Postgresql Explain: Conclusions