Re: Handling large number of OR/IN conditions

Steve Atkins <steve(at)blighty(dot)com> writes:
> On May 1, 2009, at 2:42 PM, David Wall wrote:
>> Does anybody know if PG will perform better with the table join
>> instead of evaluating the series of OR/IN? The OR/IN has to be
>> parsed, but the comparisons may be faster than the table join.

> It used to be that populating and then joining with a temporary table
> was faster than using IN (1, 3, 5, 7, 9, 11, 13) for all but the
> smallest sets. That's no longer true, and IN() is pretty good.

> I'd still use a temporary table myself, though. It's cleaner and
> easier to populate one than to cleanly produce a statement with a
> variable number of identifiers in it. And you can reuse it for
> multiple reports, join against it different ways and so on. Also you
> can populate it either from your UI or by selecting from the
> relationships table suggested above (create temporary table foo as
> select peon from reports where overlord in ('bob', 'ben', 'jerry) ),
> and still run the same reports against it.

Possibly worth noting: if you're depending on the quality of join plans
involving such a table, it's worth doing an ANALYZE against it after
you populate it. (Autovacuum won't do that for you, because it can't
access temp tables.)

regards, tom lane