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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-12-11 18:20:17 | Re: trouble with query |
Previous Message | Leandro Fanzone | 2001-12-11 17:33:07 | Re: Multiple IN |