Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS public.groupst
(
  grid varchar,
  id2 varchar,
  CONSTRAINT groupst_pkey PRIMARY KEY (id2, grid)
);

select *
from pg_indexes
where tablename = 'groupst';

CREATE TABLE IF NOT EXISTS public.ids
(
  id1 varchar,
  id2 varchar,
  CONSTRAINT ids_pkey PRIMARY KEY (id2, id1)
);

select *
from pg_indexes
where tablename = 'ids';


CREATE TABLE




schemaname
tablename
indexname
tablespace
indexdef


public
groupst
groupst_pkey
null
CREATE UNIQUE INDEX groupst_pkey ON public.groupst USING btree (id2, grid)



SELECT 1



CREATE TABLE




schemaname
tablename
indexname
tablespace
indexdef


public
ids
ids_pkey
null
CREATE UNIQUE INDEX ids_pkey ON public.ids USING btree (id2, id1)



SELECT 1


CREATE OR REPLACE FUNCTION unique_rand_5()
RETURNS SETOF integer AS
$body$
DECLARE
    nrnr    int := trunc(random()*4+1);  -- number of numbers
BEGIN

    RETURN QUERY
    SELECT (trunc(random()*4+1))::integer
    FROM   generate_series(1, nrnr*2)
    GROUP  BY 1
    LIMIT  nrnr;

END;
$body$ LANGUAGE plpgsql VOLATILE;

INSERT INTO groupst
SELECT 
    'testid:' || (t.n)::varchar AS grid,
    'val' || (unique_rand_5())::varchar AS val
FROM
    (
        SELECT
            (random()*4 + 1)::integer AS c,
            n AS n
        FROM generate_series (1,15) AS x(n)
    ) t
;

INSERT INTO ids
  SELECT
      'testid:' || gen.id::varchar AS id1,
      'val' || id2.id2::varchar AS id2
  FROM (
    SELECT generate_series(1,100000) AS id
  ) gen,
  (
    SELECT generate_series(1,5) AS id2
  ) id2
;
ANALYSE;

CREATE FUNCTION



INSERT 0 30



INSERT 0 500000



ANALYZE


EXPLAIN (ANALYSE, BUFFERS)
    SELECT
        ids.id1,
        groupst.grid,
        array_agg(ids.id2)
    FROM ids
    INNER JOIN groupst
        ON ids.id2 = groupst.id2
    GROUP BY ids.id1, groupst.grid
      -- LIMIT 200
    ;


QUERY PLAN


GroupAggregate  (cost=28944.11..351698.49 rows=1479720 width=53) (actual time=998.937..10008.509 rows=1500000 loops=1)

  Group Key: ids.id1, groupst.grid

  Buffers: shared hit=530 read=2779 dirtied=2950 written=2679, temp read=1713 written=1724

  ->  Incremental Sort  (cost=28944.11..310697.49 rows=3000600 width=26) (actual time=998.915..8144.854 rows=3000000 loops=1)

        Sort Key: ids.id1, groupst.grid

        Presorted Key: ids.id1

        Full-sort Groups: 50000  Sort Method: quicksort  Average Memory: 29kB  Peak Memory: 29kB

        Buffers: shared hit=530 read=2779 dirtied=2950 written=2679, temp read=1713 written=1724

        ->  Nested Loop  (cost=28941.70..143426.43 rows=3000600 width=26) (actual time=998.694..2617.400 rows=3000000 loops=1)

              Buffers: shared hit=530 read=2779 dirtied=2950 written=2679, temp read=1713 written=1724

              ->  Gather Merge  (cost=28941.55..87174.79 rows=500000 width=17) (actual time=998.572..1623.716 rows=500000 loops=1)

                    Workers Planned: 2

                    Workers Launched: 2

                    Buffers: shared hit=523 read=2777 dirtied=2949 written=2679, temp read=1713 written=1724

                    ->  Sort  (cost=27941.52..28462.36 rows=208333 width=17) (actual time=855.203..973.654 rows=166667 loops=3)

                          Sort Key: ids.id1

                          Sort Method: external merge  Disk: 5432kB

                          Buffers: shared hit=523 read=2777 dirtied=2949 written=2679, temp read=1713 written=1724

                          Worker 0:  Sort Method: external merge  Disk: 4304kB

                          Worker 1:  Sort Method: external merge  Disk: 3968kB

                          ->  Parallel Seq Scan on ids  (cost=0.00..5263.33 rows=208333 width=17) (actual time=0.049..197.055 rows=166667 loops=3)

                                Buffers: shared hit=405 read=2775 dirtied=2949 written=2679

              ->  Memoize  (cost=0.15..0.29 rows=8 width=14) (actual time=0.000..0.001 rows=6 loops=500000)

                    Cache Key: ids.id2

                    Cache Mode: logical

                    Hits: 499995  Misses: 5  Evictions: 0  Overflows: 0  Memory Usage: 2kB

                    Buffers: shared hit=7 read=2 dirtied=1

                    ->  Index Only Scan using groupst_pkey on groupst  (cost=0.14..0.28 rows=8 width=14) (actual time=0.021..0.024 rows=6 loops=5)

                          Index Cond: (id2 = (ids.id2)::text)

                          Heap Fetches: 30

                          Buffers: shared hit=7 read=2 dirtied=1

Planning:

  Buffers: shared hit=133 read=8

Planning Time: 1.014 ms

Execution Time: 10112.083 ms



EXPLAIN