Re: slow IN() clause for many cases

Greg Stark <gsstark(at)mit(dot)edu> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> Why? They certainly wouldn't be any slower than they are now.

> Well currently they get rewritten to use OR which does a single index scan

Not in 8.1 it doesn't; that code is long gone.

2005-04-24 21:30 tgl

Remove support for OR'd indexscans internal to a single IndexScan
plan node, as this behavior is now better done as a bitmap OR
indexscan. This allows considerable simplification in
nodeIndexscan.c itself as well as several planner modules concerned
with indexscan plan generation. Also we can improve the sharing of
code between regular and bitmap indexscans, since they are now
working with nigh-identical Plan nodes.

> The bitmap scan has to traverse all the index entries for matching records
> before it can fetch the first record. So it wouldn't be a fast-start
> plan.

If the fraction of records matching the IN-list is so large as to make
that an issue, I'd think the planner would prefer a seqscan anyway.
Besides which, it's a bit silly to worry about whether a plan is
fast-start without taking into account the amount of planner time needed
to create it.

Another point here is that LIMIT without any ORDER BY isn't an amazingly
useful case. Neither the old implementation of OR indexscans nor the
new can produce ordered output, which means you're not going to get
fast-start behavior anyway for real queries.

regards, tom lane