Re: Optimizer regression

Jim Nasby <jim@nasby.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'tknow if it's been fixed or not. I'm hoping that someone *cough*Tom*cough* would quickly recognize whether this
pushinto subquery issue has been fixed or not, so I haven't included full details or a test case. I have a work-around
soI 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