IN(subselect returning few values ...)

All,

* Josh Berkus (josh(at)agliodbs(dot)com) wrote:
> > The plain non-VALUES list form is also significantly faster than it
> > was, but I think it will only result in a bitmap indexscan plan type.
>
> Yeah, even bitmapscans break down at 1000 values ...

In a similar vein, perhaps 8.2 fixes this but I don't recall seeing
anything where it would...

Working on 8.1 I've recently been annoyed at the need to translate a
sub-select inside an IN () clause into a fixed list of contents (the
results of the sub-select, exactly) in order to get better performance.
If the results of a sub-select are very likely to be less than 1000 (or
what have you) is there a reason not to translate that sub-select into
a VALUES list or IN (constants) set (ie: a nest-loop or a bitmap
indexscan)?

This particular case was involving 9 values from a table which only had
around 250 rows total being used to find a set of records in a much,
much bigger table (60M or so, iirc). I dislike having to hard-code
those values in the scripts I'm writing, or hack it up to implement
getting the list and then using it as a constant.

A similar case I've seen is that when using a sub-select or similar
instead of a list of constants the 'One-Time Filter: false' doesn't
appear to ever be able to happen. I might have overlooked something
else which doesn't something similar, but if not this ends up making a
query *much* more expensive when alot of disjoint tables are involved,
most of which don't need to be considered since they're not in the
constants list.

Thanks,

Stephen