Monitoring Postgres EXPLAIN plans

The Basics

First time looking at EXPLAIN, or trying to understand how query plans work?

Start here: The Basics of Postgres Query Planning

Postgres Plan Nodes

Understanding the behavior and performance of individual plan nodes (and when Postgres chooses them for a plan) is critical to understanding overall query planning.

Node types can be broadly considered in three categories:

• Scan nodes: Produce rows from underlying table data
• Join nodes: Combine rows from child nodes
• Other nodes: Broad variety of functionality (e.g. aggregation, limiting, grouping, etc)

Query Plan Visualization

pganalyze includes built-in visualization of query plans, automatically collected from the Postgres logs:

Set up automatic EXPLAIN plan collection

Learn how to enable automatic EXPLAIN plan collection.

pganalyze EXPLAIN Insights

Wondering how you should optimize a particular query?

pganalyze automatically analyzes EXPLAIN plans to find the most important insights:

• Disk Sort
  When a Sort operation spills to disk due to low work_mem settings
• Expensive
  When particular nodes are more expensive than others in a plan
• Hash Batches
  When a Hash operation spilles to disk due to low work_mem settings
• Inefficient Index
  When an index is inefficient because it's loading too much data and then filters rows without an index
• I/O Heavy
  When most plan I/O happens in just a few plan nodes
• Large Offset
  When OFFSET is used for pagination, instead of an efficient method such as keyset-based pagination
• Lossy Bitmaps
  Bitmap Heap Scan that utilizes a lossy bitmap due to low work_mem
• Mis-Estimate
  The Postgres planner mis-estimated the number of rows a particular plan node returns
• Slow Scan
  Sequential Scan that removed a significant number of rows (an index would have helped to avoid this)
• Stale Stats
  The table referenced has not had an ANALYZE run recently (potentially leading to inefficient plans)