Plan for in with one argument - Mailing list pgsql-general

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?

pondDump=> explain analyze  select
     pic.objectid as pic_objectid
from
     pond_item_common pic
where
     pic.pond_user IN (select pu2.objectid from pond_user pu2 where
username_locase IN ('iceberger'))
limit 100;
                                                                   QUERY
PLAN

----------------------------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=15.41..396.89 rows=100 width=4) (actual time=0.047..0.061
rows=11 loops=1)
    ->  Nested Loop  (cost=15.41..1400.19 rows=363 width=4) (actual
time=0.046..0.056 rows=11 loops=1)
          ->  HashAggregate  (cost=8.28..8.29 rows=1 width=4) (actual
time=0.026..0.026 rows=1 loops=1)
                ->  Index Scan using pond_user_c2 on pond_user pu2
(cost=0.00..8.27 rows=1 width=4) (actual time=0.017..0.018 rows=1 loops=1)
                      Index Cond: ((username_locase)::text =
'iceberger'::text)
          ->  Bitmap Heap Scan on pond_item_common pic
(cost=7.13..1387.36 rows=363 width=8) (actual time=0.015..0.024 rows=11
loops=1)
                Recheck Cond: (pic.pond_user = pu2.objectid)
                ->  Bitmap Index Scan on pond_item_common_x1
(cost=0.00..7.04 rows=363 width=0) (actual time=0.012..0.012 rows=11
loops=1)
                      Index Cond: (pic.pond_user = pu2.objectid)
  Total runtime: 0.181 ms
(10 rows)

pondDump=> explain analyze
select
     pic.objectid as pic_objectid
from
     pond_item_common pic
where
     pic.pond_user = (select pu2.objectid from pond_user pu2 where
username_locase IN ('iceberger'))
limit 100;
                                                              QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=15.41..395.88 rows=100 width=4) (actual time=0.043..0.055
rows=11 loops=1)
    InitPlan 1 (returns $0)
      ->  Index Scan using pond_user_c2 on pond_user pu2
(cost=0.00..8.27 rows=1 width=4) (actual time=0.017..0.018 rows=1 loops=1)
            Index Cond: ((username_locase)::text = 'iceberger'::text)
    ->  Bitmap Heap Scan on pond_item_common pic  (cost=7.13..1388.27
rows=363 width=4) (actual time=0.042..0.053 rows=11 loops=1)
          Recheck Cond: (pond_user = $0)
          ->  Bitmap Index Scan on pond_item_common_x1  (cost=0.00..7.04
rows=363 width=0) (actual time=0.038..0.038 rows=11 loops=1)
                Index Cond: (pond_user = $0)
  Total runtime: 0.096 ms
(9 rows)

pondDump=>

Best regards,
Marcus