PGCon2018 - 2.5

PGCon 2018
The PostgreSQL Conference

Robert Haas
Day Talks - Day 2: Friday - 2018-06-01
Room DMS 1160
Start time 16:00
Duration 00:45
ID 1197
Event type Lecture
Track Hacking
Language used for presentation English

What's in a Plan?

And how did it get there, anyway?

PostgreSQL's EXPLAIN command is a powerful tool, allowing users to expect and understand query plans in detail. However, the output produced by EXPLAIN is not a direct representation of the plan; it omits some information and displays other information in modified form. In this talk, I'll discuss the differences between the plan as displayed by EXPLAIN and what is actually stored in the server's internal data structures. I'll also discuss why the plan includes the information it does, how that information is computed, and how that information is used at execution time.

In my 2010 talk "The PostgreSQL Query Planner", I focused primarily on the types of plans that the planner was capable of generating and the reasons why it was necessary to compare various alternatives. Tom Lane's 2011 talk "Hacking the Query Planner" discussed the overall order of operations in the planner and explained some of the motivation behind it. In this talk, I will focus less on the overall operation planner and more on the data structure which it produces as output, namely, a tree of Plan nodes. I hope that this will be helpful in understanding PostgreSQL's execution-time behavior as well as in learning to write patches that touch the planner and executor.