Передача массива (коллекции чисел) на вход функции, использование в SQL

create table dat(
id_dict integer not null constraint fk_dat_pok  references d_dict(id),
id_year integer not null constraint fk_dat_year references d_years(id),
val     numeric,
constraint uk_data unique (id_dict,id_year)
);

CREATE OR REPLACE FUNCTION study.test_collection_param(
                                                       INOUT refcur refcursor,
                                                       in_coll_n int[]
                                                      )
 RETURNS refcursor
 LANGUAGE plpgsql
 STABLE SECURITY DEFINER
AS $function$
begin
open refcur for
         select d.id_dict,
                sum(1)   as cnt,
                sum(val) as val
           from study.dat d
          where d.id_dict = ANY(in_coll_n)
          group by d.id_dict;
end;
$function$

Пример вызова

begin;
 select study.test_collection_param('rc',ARRAY[1,2,3]);

FETCH ALL IN rc;

Сравнение планов, при использовании коллекции в подзапросе:

 Вариант 1.

        explain analyze
         select d.id_dict,d.val
           from study.dat d
          where d.id_dict = ANY(ARRAY[1,2,3])

План.

Bitmap Heap Scan on dat d  (cost=13.23..23.93 rows=51 width=8) (actual time=0.018..0.033 rows=51 loops=1)
  Recheck Cond: (id_dict = ANY ('{1,2,3}'::integer[]))
  Heap Blocks: exact=9
  ->  Bitmap Index Scan on uk_data  (cost=0.00..13.21 rows=51 width=0) (actual time=0.014..0.014 rows=51 loops=1)
        Index Cond: (id_dict = ANY ('{1,2,3}'::integer[]))
Planning time: 0.050 ms
Execution time: 0.052 ms

 Execution time: 0.088 ms
 Execution time: 0.061 ms
 Execution time: 0.056 ms

 Вариант 2.

        explain analyze
         select d.id_dict,d.val
           from study.dat d
         where d.id_dict in (select unnest(array[1,2,3]))

План.

Hash Semi Join  (cost=2.76..53.13 rows=1700 width=8) (actual time=0.018..0.244 rows=51 loops=1)
  Hash Cond: (d.id_dict = (unnest('{1,2,3}'::integer[])))
  ->  Seq Scan on dat d  (cost=0.00..27.00 rows=1700 width=8) (actual time=0.006..0.103 rows=1700 loops=1)
  ->  Hash  (cost=1.51..1.51 rows=100 width=4) (actual time=0.007..0.007 rows=3 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 9kB
        ->  Result  (cost=0.00..0.51 rows=100 width=0) (actual time=0.004..0.005 rows=3 loops=1)
Planning time: 0.096 ms
Execution time: 0.261 ms

 Execution time: 0.296 ms
 Execution time: 0.266 ms
 Execution time: 0.270 ms