Dimitriy

explain (analyze) select unnest(tags) from (SELECT tags FROM t5 WHERE tags @> array['param-1'] group by 1) as t group by 1;
                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=14655.30..14660.38 rows=1000 width=84) (actual time=490.721..490.722 rows=10 loops=1)
   ->  Subquery Scan on t  (cost=14647.62..14652.80 rows=1000 width=84) (actual time=490.690..490.706 rows=55 loops=1)
         ->  HashAggregate  (cost=14647.62..14647.73 rows=10 width=84) (actual time=490.676..490.676 rows=10 loops=1)
               ->  Seq Scan on t5  (cost=0.00..13459.00 rows=475450 width=84) (actual time=0.033..181.649 rows=475324 loops=1)
                     Filter: (tags @> '{param-1}'::text[])
                     Rows Removed by Filter: 24676
 Total runtime: 490.843 ms
(7 rows)

explain (analyze) SELECT unnest(tags) FROM t5 WHERE tags @> array['param-1'] group by 1;
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=368857.88..368862.95 rows=1000 width=84) (actual time=1196.758..1196.759 rows=10 loops=1)
   ->  Seq Scan on t5  (cost=0.00..249995.38 rows=47545000 width=84) (actual time=0.020..680.547 rows=2501231 loops=1)
         Filter: (tags @> '{param-1}'::text[])
         Rows Removed by Filter: 24676
 Total runtime: 1196.790 ms
(5 rows)

explain (analyze) select unnest(tags) from (SELECT tags FROM t5 WHERE tags @> array['param-1'] group by 1) as t group by 1;
                                                                      QUERY PLAN                                                                      
------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=19433.16..19438.24 rows=1000 width=84) (actual time=411.248..411.249 rows=10 loops=1)
   ->  Subquery Scan on t  (cost=19425.49..19430.66 rows=1000 width=84) (actual time=411.220..411.233 rows=55 loops=1)
         ->  HashAggregate  (cost=19425.49..19425.59 rows=10 width=84) (actual time=411.205..411.206 rows=10 loops=1)
               ->  Bitmap Heap Scan on t5  (cost=5084.74..18236.86 rows=475450 width=84) (actual time=74.696..126.809 rows=475324 loops=1)
                     Recheck Cond: (tags @> '{param-1}'::text[])
                     ->  Bitmap Index Scan on t5_tagx_gist  (cost=0.00..4965.87 rows=475450 width=0) (actual time=73.514..73.514 rows=475324 loops=1)
                           Index Cond: (tags @> '{param-1}'::text[])
 Total runtime: 411.337 ms
(8 rows)

create table t5 as (select i id, rand_array() tags from generate_series(1,500000) as i);
create index t5_tagx_gin on t5 using gin (tags);

create or replace function rand_array() returns text[][] as $$
begin
 return (select array_agg(t1.col1) from (select format('param-%s', i) col1 from generate_series(1,rand(10)) as i) as t1);
end;
$$ language 'plpgsql' strict;