EXPLAIN ( ANALYZE, VERBOSE, COSTS, BUFFERS, TIMING ) SELECT fu.w2_page_idxs FROM fact_users AS fu WHERE EXISTS ( SELECT FROM ( SELECT ARRAY[idx] AS page_idx FROM fact_pages WHERE attribute_idxs && ARRAY[300000160] FETCH FIRST 1 ROWS ONLY ) AS fp WHERE fu.w2_page_idxs && fp.page_idx ) ;
Without any surprises, the planner is using a sequential scan on the "fact_users" table which is very large instead of using the GIN index set on the "w2_page_idxs" column.
For me, using the subquery in and expression, instead of the EXISTS, does get it to use the gin index. And I think it must give the same results.
SELECT fu.w2_page_idxs FROM fact_users AS fu WHERE fu.w2_page_idxs && ARRAY[(select idx from fact_pages where attribute_idxs && ARRAY[3003] FETCH FIRST 1 ROWS ONLY)];
But why are you using intarray? That is unnecessary here, and by creating ambiguity about the array operators it might be harmful.