Skip site navigation (1) Skip section navigation (2)

Re: Multiple IN

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Leandro Fanzone <leandro(at)hasar(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Multiple IN
Date: 2001-12-11 18:15:56
Message-ID: 18007.1008094556@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
Leandro Fanzone <leandro(at)hasar(dot)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

In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2001-12-11 18:20:17
Subject: Re: trouble with query
Previous:From: Leandro FanzoneDate: 2001-12-11 17:33:07
Subject: Re: Multiple IN

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group