Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TEMP TABLE quick_test AS 
SELECT array_agg((random() * (2^63 -1))::bigint) AS a
     , array_agg((random() * (2^63 -1))::bigint) AS b
FROM   generate_series(1, 1000000) g
GROUP  BY g%20000;

SELECT 20000


-- a_horse's function
create function concat_unique(p_array_one bigint[], p_array_two bigint[])
  returns bigint[]
as
$$
select array_agg(x order by x)
from (
  select x
  from unnest(p_array_one) as t(x)
  union
  select x
  from unnest(p_array_two) as t(x)
) t
$$
language sql
immutable;

CREATE FUNCTION


-- erwin 1
CREATE OR REPLACE FUNCTION f_merge_uniq(bigint[], bigint[])
  RETURNS bigint[] AS
$func$
SELECT ARRAY(
  SELECT unnest($1)
  UNION
  SELECT unnest($2)
   )
$func$  LANGUAGE sql IMMUTABLE;

CREATE FUNCTION


-- erwin 2
CREATE OR REPLACE FUNCTION f_merge_uniq_sort(bigint[], bigint[])
  RETURNS bigint[] AS
$func$
SELECT ARRAY(
   SELECT DISTINCT x
   FROM (
      SELECT unnest($1)
      UNION ALL 
      SELECT unnest($2)
      ) sub(x)
   ORDER BY 1
   )
$func$  LANGUAGE sql IMMUTABLE;

CREATE FUNCTION


VACUUM FULL ANALYZE quick_test; -- to rule out artefacts

VACUUM


EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT  concat_unique(a, b) FROM quick_test;


QUERY PLAN


Seq Scan on quick_test (actual rows=20000 loops=1)

Planning Time: 0.255 ms

Execution Time: 1392.805 ms



EXPLAIN


EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT  f_merge_uniq(a, b) FROM quick_test;


QUERY PLAN


Seq Scan on quick_test (actual rows=20000 loops=1)

Planning Time: 0.084 ms

Execution Time: 889.644 ms



EXPLAIN


EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT  f_merge_uniq_sort(a, b) FROM quick_test;


QUERY PLAN


Seq Scan on quick_test (actual rows=20000 loops=1)

Planning Time: 0.094 ms

Execution Time: 827.999 ms



EXPLAIN