Monitoring Postgres EXPLAIN plans
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
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
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
ANALYZErun recently (potentially leading to inefficient plans)
Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →