IN(subselect returning few values ...) - Mailing list pgsql-hackers

From Stephen Frost
Subject IN(subselect returning few values ...)
Date
Msg-id 20061101142604.GI24675@kenobi.snowman.net
Whole thread Raw
Responses Re: IN(subselect returning few values ...)
List pgsql-hackers
All,

* Josh Berkus (josh@agliodbs.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

pgsql-hackers by date:

Previous
From: tomas@tuxteam.de
Date:
Subject: Re: [GENERAL] Index greater than 8k
Next
From: "Henry B. Hotz"
Date:
Subject: Re: Design Considerations for New Authentication Methods