How to dive into the PostgreSQL's optimizer and trace each step it takes to optimize the query?

Just like the MEMO structure in SQL Server which is kind of a "paper trail" of steps the optimizer takes in optimizing the query. Does PostgreSQL have the similar structure which records steps the optimizer takes?

No, there's no detailed optimizer audit/reporting structure or function.

Your best tool is 'gdb'. Unlike MS SQL Server, PostgreSQL is open source; you can just build with debug symbols enabled, attach a debugger, and trace exactly what it's doing.

That doesn't mean it's easy, of course. A canned set of breakpoints and gdb macros to produce a trace of what the planner/optimiser are doing would be very nice. I'm not aware of any such tool at this point.

gdb's tracepoints, or perf userspace tracepoints, will be very useful to you.

You can also enable detailed debug logging and some of the trace parameters in postgresql.conf then examine the resulting detailed log files.

There's some documentation (which I've added info on dumping plan trees, etc, to) in the PostgreSQL developer FAQ: https://wiki.postgresql.org/wiki/Developer_FAQ . This may be useful. You should also read the main developer docs.

StackExchange Database Administrators Q#42720, answer score: 3