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.

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.

How about:

WITH myCTE AS(  SELECT ext_id  FROM aggregate_table  ORDER BY somedata  LIMIT 10)SELECT *FROM myCTELEFT JOIN table1  ON myCTE.ext_id = table1.idORDER BY somedata