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.