Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- test setup with ~ 51k rows, avg ~ 21 elements per array (~ 5 % NULL), ~ 4% NULL arrays, in random order
CREATE TABLE example AS
SELECT (r*50000)::int AS sort, array_agg(d) AS date_array
FROM (
   SELECT random() AS r
        , (CURRENT_DATE - (random()*10000)::int)::timestamp AS d  -- dates in ~ last 30 years
   FROM   generate_series(1, 1000000) g
   UNION  ALL
   SELECT random()
        , NULL::timestamp
   FROM   generate_series(1, 50000) g
   ORDER  BY r
   ) sub1
GROUP  BY 1
UNION  ALL
SELECT (random()*50000)::int AS sort
     , NULL::timestamp[]
FROM   generate_series (1, 2000)
ORDER  BY 1;
52001 rows affected

VACUUM ANALYZE example;
-- klin
EXPLAIN (ANALYZE, TIMING OFF)
select array_agg(distinct elem order by elem desc) as result
from example
cross join unnest(date_array) as elem
where elem is not null;


QUERY PLAN


Aggregate  (cost=13626.24..13626.25 rows=1 width=32) (actual rows=1 loops=1)

  ->  Nested Loop  (cost=0.00..12326.21 rows=520010 width=8) (actual rows=1000000 loops=1)

        ->  Seq Scan on example  (cost=0.00..1926.01 rows=52001 width=188) (actual rows=52001 loops=1)

        ->  Function Scan on unnest elem  (cost=0.00..0.10 rows=10 width=8) (actual rows=19 loops=52001)

              Filter: (elem IS NOT NULL)

              Rows Removed by Filter: 1

Planning Time: 0.135 ms

Execution Time: 429.591 ms


-- erwin
EXPLAIN (ANALYZE, TIMING OFF)
SELECT ARRAY(
   SELECT DISTINCT elem
   FROM  (SELECT unnest(date_array) FROM example) AS e(elem)
   WHERE  elem IS NOT NULL
   ORDER  BY elem DESC
   );


QUERY PLAN


Result  (cost=11419.84..11419.85 rows=1 width=32) (actual rows=1 loops=1)

  InitPlan 1 (returns $0)

    ->  Sort  (cost=11419.34..11419.84 rows=200 width=8) (actual rows=10001 loops=1)

          Sort Key: e.elem DESC

          Sort Method: quicksort  Memory: 853kB

          ->  HashAggregate  (cost=11409.69..11411.69 rows=200 width=8) (actual rows=10001 loops=1)

                Group Key: e.elem

                Batches: 1  Memory Usage: 929kB

                ->  Subquery Scan on e  (cost=0.00..10116.17 rows=517410 width=8) (actual rows=1000000 loops=1)

                      Filter: (e.elem IS NOT NULL)

                      Rows Removed by Filter: 50000

                      ->  ProjectSet  (cost=0.00..4916.07 rows=520010 width=8) (actual rows=1050000 loops=1)

                            ->  Seq Scan on example  (cost=0.00..1926.01 rows=52001 width=188) (actual rows=52001 loops=1)

Planning Time: 0.133 ms

Execution Time: 262.064 ms


-- 2nd time to make sure
-- klin
EXPLAIN (ANALYZE, TIMING OFF)
select array_agg(distinct elem order by elem desc) as result
from example
cross join unnest(date_array) as elem
where elem is not null;


QUERY PLAN


Aggregate  (cost=13626.24..13626.25 rows=1 width=32) (actual rows=1 loops=1)

  ->  Nested Loop  (cost=0.00..12326.21 rows=520010 width=8) (actual rows=1000000 loops=1)

        ->  Seq Scan on example  (cost=0.00..1926.01 rows=52001 width=188) (actual rows=52001 loops=1)

        ->  Function Scan on unnest elem  (cost=0.00..0.10 rows=10 width=8) (actual rows=19 loops=52001)

              Filter: (elem IS NOT NULL)

              Rows Removed by Filter: 1

Planning Time: 0.079 ms

Execution Time: 425.791 ms


-- erwin
EXPLAIN (ANALYZE, TIMING OFF)
SELECT ARRAY(
   SELECT DISTINCT elem
   FROM  (SELECT unnest(date_array) FROM example) AS e(elem)
   WHERE  elem IS NOT NULL
   ORDER  BY elem DESC
   );


QUERY PLAN


Result  (cost=11419.84..11419.85 rows=1 width=32) (actual rows=1 loops=1)

  InitPlan 1 (returns $0)

    ->  Sort  (cost=11419.34..11419.84 rows=200 width=8) (actual rows=10001 loops=1)

          Sort Key: e.elem DESC

          Sort Method: quicksort  Memory: 853kB

          ->  HashAggregate  (cost=11409.69..11411.69 rows=200 width=8) (actual rows=10001 loops=1)

                Group Key: e.elem

                Batches: 1  Memory Usage: 929kB

                ->  Subquery Scan on e  (cost=0.00..10116.17 rows=517410 width=8) (actual rows=1000000 loops=1)

                      Filter: (e.elem IS NOT NULL)

                      Rows Removed by Filter: 50000

                      ->  ProjectSet  (cost=0.00..4916.07 rows=520010 width=8) (actual rows=1050000 loops=1)

                            ->  Seq Scan on example  (cost=0.00..1926.01 rows=52001 width=188) (actual rows=52001 loops=1)

Planning Time: 0.086 ms

Execution Time: 224.081 ms