Rowley: What’s new in the Postgres 16 query planner / optimizer

[Posted February 9, 2024 by corbet]

David Rowley looks deeply into the improvements coming to the query planner in PostgreSQL 16.

For a long time now, PostgreSQL has been able to remove a LEFT JOIN where no column from the left joined table was required in the query and the join could not possibly duplicate any rows.
However, in versions prior to PostgreSQL 16, there was no support for left join removals on partitioned tables. Why? Because the proofs that the planner uses to determine if there’s any possibility any inner-side row could duplicate any outer-side row were not present for partitioned tables.
The PostgreSQL 16 query planner now allows the LEFT JOIN removal optimization with partitioned tables.

Rowley: What’s new in the Postgres 16 query planner / optimizer

Posted Feb 12, 2024 7:51 UTC (Mon) by LtWorf (subscriber, #124958) [Link] (1 responses)

Writing optimizations is an interesting exercise.

I did it myself (at an amateur level) with my relational algebra compiler.

I remember when i proved that a certain transformation would work for product, and I decided to apply the same to joins, since they are similar enough… they weren't similar enough and the optimization generated different results in those cases.

Of course, since only students use that software, there were no consequences.

Rowley: What’s new in the Postgres 16 query planner / optimizer

Posted Feb 12, 2024 15:26 UTC (Mon) by Sesse (subscriber, #53779) [Link]

An inner join is exactly equivalent to a cross-product followed by a filter on the join condition. Outer joins, however, follow different and sometimes more complex rules.