Re: Optimizer regression

Jim Nasby <jim(at)nasby(dot)net> writes:
> Just upgraded to 8.4 (I know, I know) and ran across this. Unfortunately I have no way to test this on 9.x, so I don't know if it's been fixed or not. I'm hoping that someone *cough*Tom*cough* would quickly recognize whether this push into subquery issue has been fixed or not, so I haven't included full details or a test case. I have a work-around so I don't care about this in 8.4, but if this regression still exists it would be nice if it were fixed.

It's hard to be sure with such an incomplete example, but I think 8.4 is
flattening the EXISTS to a semijoin and then getting trapped by join
order constraints into doing something less than optimal for this
particular use-case. It was this type of example that motivated the
"parameterized path" stuff I've been working on for the past couple
of years.

In short, 9.2 should produce at least as good a plan as 8.3 for this
example, but 8.4 through 9.1 might not.

BTW, your workaround looks wrong --- you need to constrain the outside
of the left join not the inside, no?

regards, tom lane