In article <20081014064831.GB22137@xxxxxxxxxxxxxxx>,
"A. Kretschmer" <andreas.kretschmer@xxxxxxxxxxxxxx> writes:
> 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.
I think that "OR or IN" is the wrong question. Where do those >50
values come from? If they come from a DB operation, just include this
operation in your query. Otherwise, you should ask yourself how many
values you might get: >50, >500, >5000? There's a point where it's
probably more efficient to COPY all those values into an indexed
temporary table, ANALYZE it, and JOIN it into your query.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
