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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
