Re: BUG #16885: PostgreSQL upgrade to 12+ changes hash join to slow nested loop

On Mon, Feb 22, 2021 at 11:56 AM Magnus Hagander <magnus(at)hagander(dot)net>
wrote:

>
> You'll have to post your actual query to get proper feedback.
>
> That said, a typical case for this specifically when going to version
> 12 would be that CTEs are no longer optimization barriers. If you do
> use CTEs in your query, try changing them from "WITH x AS (...)" to
> "WITH x AS MATERIALIZED (...)" to return to the previous behaviour and
> see if that improves things.
>

I don't know if I will be allowed to publicly post the actual query. I can
say it's quite complicated, and involves the following features:

* an inner SELECT DISTINCT from a particular primary table and some joins
* a join to the same primary table in the outer query, among others
* a join to a view in the outer query
* said view uses CTEs like you say, though based on unrelated tables
* then said view makes a UNION of 2 different SELECTs
* each of these again joins to the same primary table

So I'm not surprised the query planner gets confused. And your trick works.
Materializing those CTEs makes the query planner choose the hash join again.

I still think there is a bug here though. The query planner is getting
something really wrong, and at least now we know it's a consequence of
trying to optimize through the CTEs.

I will see what I can do about sharing the actual query. If this is
interesting to a pg developer, you're welcome to contact me.

Best,
Tavin