Understanding EXPLAIN: Reading PostgreSQL Query Plans
Read a PostgreSQL execution plan in depth, comparing the planner's estimates with what the executor measured. Leave with a three-step method for finding why a query is slow.
- €200per person, before VAT
- 1 live session3 hours 30 minutes
- Theory and live demoswith Q&A
- Live online via Zoomin English
- Certificate of Attendance
Upcoming dates
What you'll learn
The longest session of the developer track. It takes EXPLAIN ANALYZE output apart node by node and turns it into a diagnostic workflow you can apply to your own queries.
The course covers
- EXPLAIN options and when to use each: ANALYZE, BUFFERS, WAL, SETTINGS, MEMORY, SERIALIZE, GENERIC_PLAN and FORMAT
- Reading the execution tree: node types, cost, estimated rows and actual rows
- Scan nodes and the three join algorithms: nested loop, hash join and merge join
- Aggregation, sorting, Limit, Memoize and parallel nodes, and the signs that a sort or hash spilled to disk
- A three-step diagnosis: find the hotspot, compare estimated with actual rows, look for wasted work
- Why the planner still picks bad plans: generic and custom plans, plan_cache_mode, predicates that hide a column from an index
- Influencing plan choice, from better statistics to enable_* settings and pg_hint_plan, and when to stop
Prerequisites
For developers who need to explain why a PostgreSQL query is slow, not just see that it is.
You should be comfortable writing SQL, including joins and aggregates. The session is self-contained: no other session of the track is required.
For details see the course terms & conditions.
Syllabus
1EXPLAIN and its options
- EXPLAIN against EXPLAIN ANALYZE.
- Running EXPLAIN ANALYZE on INSERT, UPDATE or DELETE safely, inside a rolled-back transaction.
- BUFFERS (on by default since PostgreSQL 18), WAL, SETTINGS, MEMORY, SERIALIZE, GENERIC_PLAN and output formats.
2How to read EXPLAIN
- Scan nodes: sequential, index, index-only and bitmap scans.
- Nested loop, hash and merge joins.
- Aggregate strategies, sort methods, Limit, Memoize, CTE scans and parallel plans.
- A worked three-table plan.
3How reading EXPLAIN improves performance
- Find the hotspot, compare estimates to actuals, look for wasted work.
- Rows removed by filters, spilling sorts and hashes, heap fetches on index-only scans, redundant joins.
4When the optimiser makes mistakes
- Custom against generic plans, and plan_cache_mode.
- Five tools for influencing the planner, in order of preference.
- When to stop fighting the planner.
Frequently asked questions
Who is this session for?
Developers who write SQL against PostgreSQL and need to find out why a query is slow. It assumes working SQL, not knowledge of PostgreSQL internals.
Can I book this session on its own?
Yes. Every session of the developer track is self-contained. It is also one of the seven sessions of High Performance SQL with Postgres, which you can book in full at a package price.
Is the session recorded?
No. The session is live, with time for questions, and is not recorded. You receive the handouts: the slides with the trainer's commentary and the full demo transcripts.
Can my company pay by invoice?
Yes — write to connect@dataegret.com and we invoice your company; card booking is also available online.
Instructors
Over 15 years of experience across Oracle, DB2, MySQL, SQL Server and PostgreSQL. A recognised PostgreSQL contributor, active in the community through committees, user groups and international conferences.
Over 20 years of industry experience, with extensive expertise in supporting clients on their PostgreSQL journey and achieving high availability for business-critical projects.
Ready to book, or still deciding?
Live, small-group, taught by the people who run PostgreSQL in production. Questions first? Write to us — no sales pitch.