Re: OR or IN ?

In article <20081014064831(dot)GB22137(at)a-kretschmer(dot)de>,
"A. Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> 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.