This is exciting news for me. Coming from Oracle, I found out the hard way about pg materializing CTEs.
Excellent! So CTEs are no longer an optimisation fence? No reason to avoid CTEs for performance reasons.
Assuming this is coming to Pg12?
They seem to inline CTEs as Sub-SELECTs if possible, and then optimize these with the outer query as before.
The updated documentation has more info: select.sgml diff (I don’t know how to link to a specific line number in the diff…)