Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Postgresql Explain

Abhishek Yadav
September 19, 2015

Postgresql Explain

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

Abhishek Yadav

September 19, 2015

More Decks by Abhishek Yadav

Other Decks in Technology


  1. Postgresql Explain: Agenda • Relevance and justification • Syntax •

    Usage - SELECT • Index basics • Some Heuristics • Usage GROUP • Usage – JOIN • A bit about Mysql
  2. 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.
  3. Postgresql Explain: Syntax • EXPLAIN <query> • EXPLAIN ANALYSE <query>

    – BEGIN; EXPLAIN ANALYSE <query>; ROLLBACK • ActiveRecord::Relation#explain
  4. 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
  5. Postgresql Explain: Heuristics • Seq scan on large tables =>

    An index can help • Seq scan on indexed tables => Wrongly indexed/ low-cardinality
  6. Postgresql Explain: Join • INNER JOIN with GROUP and ORDER

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

    BY – With filters: 10k posts, 50k comments
  8. 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
  9. 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