Re: OR or IN ?

am Tue, dem 14.10.2008, um 8:33:21 +0200 mailte Luca Ferrari folgendes:
> Hi all,
> I've got a query with a long (>50) list of ORs, like the following:
>
> SELECT colB, colC FROM table WHERE colA=X OR colA=Y OR colA=Z OR ....
>
> Is there any difference in how postgresql manages the above query and the
> following one?
>
> SELECT colB, colC FROM table WHERE colA IN (X,Y,Z,...)

Depends on the version: (same table foo)

8.1:
test=*# explain select * from foo where a in (1,2,3);
QUERY PLAN
-----------------------------------------------------
Seq Scan on foo (cost=0.00..47.45 rows=32 width=4)
Filter: ((a = 1) OR (a = 2) OR (a = 3))

8.4devel:
test=# explain select * from foo where a in (1,2,3);
QUERY PLAN
-----------------------------------------------------
Seq Scan on foo (cost=0.00..43.00 rows=36 width=4)
Filter: (a = ANY ('{1,2,3}'::integer[]))

As you can see, 8.1 rewrite the query to many ORs.

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net