Re: Plan for in with one argument

On 7/11/10 12:42 , Alban Hertroys wrote:
> On 11 Jul 2010, at 11:38, Marcus Engene wrote:
>
>
>> Hi List,
>>
>> With automated queries where I have COLUMN IN (), I get a different plan from COLUMN = (). That would make sense if there are several arguments, but in the case with just one argument the penalty can be seveare. One query went from 5s execution time to a few houndreds of mS when I changed IN to = if the number of arguments is 1.
>>
>> Is there a technical reason for not treating IN with one argument as = in that case?
>>
> It does that already for constant IN-lists:
> => create table test (id serial PRIMARY KEY);
> => insert into test (id) SELECT nextval('test_id_seq') from generate_
> series(1, 10000);
> => ANALYZE test;
> => explain analyse select * from test where id IN (15);
> QUERY PLAN
> --------------------------------------------------------------------------------
> Index Scan using test_pkey on test (cost=0.00..8.27 rows=1 width=4) (actual time=0.024..0.029 rows=1 loops=1)
> Index Cond: (id = 15)
> Total runtime: 0.102 ms
> (3 rows)
>
>
> However, you're using a subquery to get the IN-list. I'm pretty sure the planner cannot know for certain that your subquery will return only one row, so it cannot substitute your IN(subquery) with =(subquery).
>
> You'd probably be better off using an EXISTS instead of an IN there, that should certainly help for cases where the subquery returns many records, but it also gives the planner a better idea of your intentions.
>
>

Hi Alban,

This makes great sense both in theory and empiric tests. Thanks for the
explanation.

Best regards,
Marcus