Re: Strange query optimization in 7.3.2

Alec Mitchell <apm13(at)columbia(dot)edu> writes:
> With enable_seqscan set to off, I get the fastest query plan (at least with
> the patched version, unpatched I have to turn off both enable_hashjoin and
> enable_mergejoin). It looks like the reason the fast nested loop isn't
> choosen by default is that the planner estimates a cost of 477.54 per loop
> for 62 loops, whereas the actual cost is 8.34 per loop for 52 loops.

This is a known issue --- the planner overestimates the cost of a
nestloop with inner indexscan, because it doesn't allow for the fact
that the successive inner indexscans aren't independent. (The upper
btree layers, at least, are sure to stay in memory over the successive
probes of the inner table ... but the costing charges for a from-scratch
indexscan each time through.) We've discussed this before but I don't
think anyone's found an appropriate substitute equation.

regards, tom lane