Re: Multiple IN

Leandro Fanzone <leandro@hasar.com> writes:
> The problem is that I have to match the pair, so my SQL again would be
> SELECT f3 FROM t
> WHERE (f1 = 1 AND f2 = 2) OR (f1 = 2 AND f2 = 1) OR (f1 = 8 OR f2 = 5);
> for the former example. I can guarantee that all the values I put in the OR
> list exist in the table, and I want to know the f3 of each one, exactly. The
> values from the list are arbitrary or random, and are not coming from a
> SELECTion. I want to avoid the AND/OR list, because the vector could be very
> long.

Actually, that is the standard way of doing it, and the performance is
likely to be less bad than you think.  If f1/f2 are indexed (which they
would be, since they're the primary key) you should get a plan like
this:

regression=# create table foo (f1 int, f2 int, primary key (f1,f2));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo'
CREATE
regression=# explain
regression-# SELECT * FROM foo WHERE
regression-# (f1 = 1 and f2 = 2) or
regression-# (f1 = 3 and f2 = 4) or
regression-# (f1 = 1 and f2 = 4);
NOTICE:  QUERY PLAN:

Index Scan using foo_pkey, foo_pkey, foo_pkey on foo  (cost=0.00..14.51 rows=1 width=8)

EXPLAIN

The multiple index scan represents three successive index probes using
the three OR'd parts of the WHERE condition.

You will eventually see it switch over to a seqscan if you get up into
many hundreds or thousands of OR'd conditions, but at that point I'd suggest
that you need to stick the probe values into a temp table and do a join.

            regards, tom lane