Schedule - Nordic PGDay 2020
FlameExplain - Building the Best EXPLAIN ANALYZE Tool out of Volcanic Ashes
Date: 2020-03-24
Time: 11:25–12:15
Room: Ballroom
If you ever had to debug a slow query in PostgreSQL, you have probably used EXPLAIN ANALYZE <query>.
EXPLAIN ANALYZE is amazing, but unfortunately it is also a little broken. This mostly isn't due to bugs, but rather due to violations of the principle of least surprise. E.g. why can a node take less time to execute than the sum of its children's execution time?
There already are many great tools that attempt to reduce these surprises, but sadly they fail to do so for complicated queries where you'd need their help the most. This slows down the work of both experienced as well as novice query optimizers.
This presentation will:
- Introduce you to the inner workings of EXPLAIN ANALYZE, including PostgreSQL's query planner and its Volcano-Style executor model.
- Explain Nested Loops, CTEs, Filters using Init Plans, Parallel Append, and other types of query plans that contain child nodes that exceed the execution time of their parents and make it seem like elephants are bad at math (they are not).
- Tell the story of building FlameExplain, a new EXPLAIN ANALYZE tool that adjusts these kinds of pathological plans to make the "numbers add up". This improves the user experience and enables Flame Graphs and other visualizations that were previously impossible to implement reliably.
