Postgresql Explain Chennai.rb meet-up 19-Sep-2015

Postgresql Explain Abhishek Yadav @h6165

Postgresql Explain: Agenda ● Relevance and justification ● Syntax ● Usage - SELECT ● Index basics ● Some Heuristics ● Usage GROUP ● Usage – JOIN ● A bit about Mysql

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.

Postgresql Explain: Syntax ● EXPLAIN ● EXPLAIN ANALYSE – BEGIN; EXPLAIN ANALYSE ; ROLLBACK ● ActiveRecord::Relation#explain

Postgresql Explain: Usage ● Simple SELECT query

Postgresql Explain: SELECT ● SELECT with WHERE (no index)

Postgresql Explain: SELECT ● SELECT with WHERE indexed

Postgresql Explain: SELECT ● SELECT with WHERE indexed (bitmap)

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

Postgresql Explain: Heuristics ● Seq scan on large tables => An index can help ● Seq scan on indexed tables => Wrongly indexed/ low-cardinality

Postgresql Explain: Group – Arbitrary example

Postgresql Explain: Join ● LEFT JOIN – low cardinality – 100 posts, 50k comments

Postgresql Explain: Join ● LEFT JOIN – better cardinality – 10k posts, 50k comments

Postgresql Explain: Join ● INNER JOIN with GROUP and ORDER BY – No filters: 10k posts, 50k comments

Postgresql Explain: Join ● INNER JOIN with GROUP and ORDER BY – With filters: 10k posts, 50k comments

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

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

Postgresql Explain: Conclusions