Re: Bad query plan inside EXISTS clause

Yeb Havinga wrote:
> Kenneth Marshall wrote:
>> EXISTS matches NULLs too and since they are not indexed a
>> sequential scan is needed to check for them. Try using
>> IN instead.
>>
> This is nonsense in more than one way.
Hit ctrl-return a bit too slow - exists does not match null but a set of
records, that is either empty or not empty. Also it is possible to index
table columns with nulls, and then the indexes can still be used.
Besides filtering record sets with expressions, indexes are also used
for ordering. There the effect of indexes with nulls can be seen: where
to put them: in front or after the non nulls? So indexes can be
perfectly used in conjunction with nulls. I found the original mail
rather intriguing and played with an example myself a bit, but could not
repeat the behavior (9.0 devel version), in my case the exists used an
index. Maybe it has something to do with the fact that the planner
estimates to return 50000 rows, even when the actual numbers list only 1
hit. In the exists case, it can stop at the first hit. In the select all
rows case, it must return all rows. Maybe a better plan emerges with
better statistics?

regards,
Yeb Havinga