Arjen van der Meijden <acmmailing@tweakers.net> writes:
> I've been mailing off-list with Tom and we found at least one
> query that in some circumstances takes a lot more time than it should,
> due to it mistakenly chosing to do a bitmap index scan rather than a
> normal index scan.
Just to clue folks in: the problem queries seem to be cases like
WHERE col1 = 'const'
AND col2 = othertab.colx
AND col3 IN (several hundred integers)
where the table has an index on (col1,col2,col3). 8.2 is generating
a plan involving a nestloop with inner bitmap indexscan on this index,
and using all three of these WHERE clauses with the index. The ability
to use an IN clause (ie, ScalarArrayOpExpr) in an index condition is
new in 8.2, and we seem to have a few bugs left in the cost estimation
for it. The problem is that a ScalarArrayOpExpr effectively causes a
BitmapOr across N index scans using each of the array elements as an
individual scan qualifier. So the above amounts to several hundred
index probes for each outer row. In Arjen's scenario it seems that
the first two index columns are already pretty selective, and it comes
out a lot faster if you just do one indexscan using the first two
columns and then apply the IN-condition as a filter to the relatively
small number of rows you get that way.
What's not clear to me yet is why the 8.2dev code didn't fall into this
same trap, because the ScalarArrayOpExpr indexing code was already there
on 3-June. But we didn't and still don't have any code that considers
the possibility that a potential indexqual condition should be
deliberately *not* used with the index.
regards, tom lane