Re: slow IN() clause for many cases

Please post an explain analyze on your query with a 20-30 item IN clause so
that we can see what plan is being generated.

On 10/11/05, Ilia Kantor <ilia(at)obnovlenie(dot)ru> wrote:
>
>
> When in clause becomes large enough (>20-30 cases),
> It is much better to use "join" way of processing..
>
> I mean,
> "SELECT * FROM table WHERE field IN (1,2...30)" will be slower than
> "SELECT * FROM table JOIN (SRF returning 1...30) USING(field)"
>
> I'm not quite sure, where the difference starts, but sometimes I need to
> make selects with 30 or more items by primary key and I get significant
> speed up by this transform:
>
> CREATE OR REPLACE FUNCTION array2table(arr int[]) RETURNS SETOF int
>
> select * from persons join (select array2table as id from
>
> array2table(array[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,2
> 3,24,25,26,27,28,29,30])) a using(id);
>
> I'm sure that backend could do that in a much faster and elegant fasion.
> Bitmap-or is nice, but for many IN arguments it is still much slower than
> join.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

--
Respectfully,

Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
http://www.enterprisedb.com/