Re: Query Optimizer::7.3 to 7.4:: in() problems

On Wed, 17 Dec 2003, Rich Seiersen wrote:

> We have noted a significant (seeming) slow down in the query optimizer from
> 7.3. to 7.4 as it relates to in() clauses. I reviewed some of Tom Lane's
> comments regarding the fact that it was actually optimized specifically - so
> our curiosity has been roused.
>
> I am wondering if there is something either in our SQL, or is there some
> sort of bug that we need to be aware of? Should we preempt the optimizer
> and break out the in clause in to joins, adding the distinct clauses - or in
> theory - should the optimizers implementation be satisfactory?
>
> Below is each query against 7.4., one with the select clause in the in()
> and the other with what would be the ids that come from the select. The
> latter is much quicker (obviously), and as stated, 7.3. is fine.
>
> Also, for reference, see page 19 of Tom's Paper here:
> http://conferences.oreillynet.com/presentations/os2003/lane_tom.pdf
>
> -the slow-down only occurs on postgres v 7.4, on 7.3 there is no performance
> problem.
>
> -it looks as if it has something to do with the planner and the way that it
> handles nested selects; maybe there was some sort of configuration change on
> the new version?
>
> Thanks for your help,

Hmm, what does the 7.3 plan look like?

I'm guessing that 7.3 is running the subselect for each of the about 40000
resume rows, but for some reason 7.4 wants to join the entire thing and
then do the condition. I'd think it's overestimating the cost of the
subselect and that's pushing it to assume that it'd be very expensive to
do it 40000 times given that it's overestimating the number of rows with
order_resume.keyf_orderid=51972 by a factor of 40. But, I'm not sure
that'd be enough to get it to choose a different plan even if fixed.