PostgreSQL IN operator with subquery poor performance

Seems that I have finally found a solution:

select * 
  from view1 
  where view1.id = ANY(
                       (select array(select ext_id 
                                     from aggregate_table 
                                     order by somedata limit 10)
                       )::integer[]
                      ) 
  order by view1.somedata;

After elaborating @Dukeling’s idea:

I suspect where id in (1,2,3,4,5,6,7,8,9,10) can be optimised and
where id in (select …) can’t, the reason being that
(1,2,3,4,5,6,7,8,9,10) is a constant expression, while the select is
not.

and locating these in faster query plan

Recheck Cond: (id = ANY ('{1,2,3,4,5,6,7,8,9,10}'::integer[]))
Index Cond: (id = ANY ('{1,2,3,4,5,6,7,8,9,10}'::integer[]))

this works even faster than the first query in the question, about 1.2ms, and now it uses

Recheck Cond: (id = ANY ($1))
Index Cond: (id = ANY ($1))

and bitmap scans in the plan.

Leave a Comment

techhipbettruvabetnorabahisbahis forumu