Postgres 14

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 TABLE IF NOT EXISTS public.complex
(
  complex_id varchar,
  CONSTRAINT complex_pkey PRIMARY KEY (complex_id)
);

CREATE TABLE IF NOT EXISTS public.complex_in
(
  complex_id varchar,
  input varchar,
  CONSTRAINT complex_in_pkey PRIMARY KEY (input, complex_id),
  CONSTRAINT complex_in_fkey FOREIGN KEY (complex_id)
    REFERENCES complex (complex_id) MATCH SIMPLE
);

CREATE TABLE IF NOT EXISTS public.complex_groupcond
(
  complex_id varchar,
  cond varchar,
  CONSTRAINT complex_groupcond_pkey PRIMARY KEY (cond, complex_id),
  CONSTRAINT complex_groupcond_fkey FOREIGN KEY (complex_id)
    REFERENCES complex (complex_id) MATCH SIMPLE
);

select *
from pg_indexes
where tablename LIKE 'complex%';

CREATE TABLE IF NOT EXISTS public.values
(
  value_id varchar,
  name varchar,
  value json,
  step integer,
  CONSTRAINT values_pkey PRIMARY KEY (name, value_id)
);

select *
from pg_indexes
where tablename = 'values';


CREATE TABLE



CREATE TABLE



CREATE TABLE




schemaname
tablename
indexname
tablespace
indexdef


public
complex
complex_pkey
null
CREATE UNIQUE INDEX complex_pkey ON public.complex USING btree (complex_id)

public
complex_in
complex_in_pkey
null
CREATE UNIQUE INDEX complex_in_pkey ON public.complex_in USING btree (input, complex_id)

public
complex_groupcond
complex_groupcond_pkey
null
CREATE UNIQUE INDEX complex_groupcond_pkey ON public.complex_groupcond USING btree (cond, complex_id)



SELECT 3



CREATE TABLE




schemaname
tablename
indexname
tablespace
indexdef


public
values
values_pkey
null
CREATE UNIQUE INDEX values_pkey ON public."values" USING btree (name, value_id)



SELECT 1


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

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

END;
$body$ LANGUAGE plpgsql VOLATILE;

INSERT INTO complex 
  SELECT 'complex_id_' || n::varchar
  FROM generate_series(1,12) n;

INSERT INTO complex_in
SELECT 
    'complex_id_' || (t.n)::varchar AS grid,
    'val' || (unique_rand_5())::varchar AS val
FROM
    (
        SELECT
            (random()*2 + 1)::integer AS c,
            n AS n
        FROM generate_series (1,12) AS x(n)
    ) t
;

INSERT INTO values
  SELECT
      'testid_' || gen.id::varchar AS id1,
      'val' || id2.id2::varchar AS id2,
      to_json(random()) AS value,
      0 AS step
  FROM (
    SELECT generate_series(1,10000) AS id
  ) gen,
  (
    SELECT generate_series(1,3) AS id2
  ) id2
;
ANALYSE;

CREATE FUNCTION



INSERT 0 12



INSERT 0 24



INSERT 0 30000



ANALYZE


INSERT INTO values
    SELECT
       values.value_id,
       complex_in.complex_id AS name,
       json_agg(values.value ORDER BY complex_in.input),
      -- array_agg(complex_in.input),
       1 AS step
    FROM values
    INNER JOIN complex_in
      ON values.name = complex_in.input
    GROUP BY values.value_id, complex_in.complex_id
    -- in the original here is a condition, if all inputs are available
HAVING count(complex_in.input) >=3;

INSERT INTO complex
SELECT name || 'new' AS complex_id
  FROM (
    SELECT DISTINCT values.name
    FROM values
    WHERE values.step >= 1
    LIMIT 2
  ) cplx_new_ids;

INSERT INTO complex_in
SELECT complex.complex_id, newnames.name
  FROM complex,
  (SELECT DISTINCT values.name FROM values) newnames
  WHERE newnames.name || 'new' = complex.complex_id
  ;

INSERT INTO complex_groupcond
SELECT complex.complex_id, 'val' || x::varchar
  FROM complex,
  (SELECT DISTINCT values.name FROM values) newnames,
  generate_series(1,2) x
  WHERE newnames.name || 'new' = complex.complex_id
  ;
ANALYSE;

INSERT 0 20000



INSERT 0 2



INSERT 0 2



INSERT 0 4



ANALYZE


EXPLAIN ANALYSE 
                SELECT
                    outereq.value_id,
                    outereq.name,
                    outereq.grp_cond
                FROM (
                    SELECT
                        DISTINCT ON (
                            values.name,
                            values.value_id,
                            complex_in.complex_id
                        )
                        values.value_id,
                        complex_in.complex_id,
                        values.name,
                        string_agg(jsonb_path_query_first(values.value::jsonb, ('$[' || complex_cond.num-1 || ']' )::jsonpath )::text, '_')
                        OVER (PARTITION BY
                                values.name,
                                values.value_id,
                                complex_in.complex_id
                            ORDER BY values.name
                            RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                            )
                         AS grp_cond
                    FROM values
                    INNER JOIN complex_in
                        ON complex_in.input = values.name
                    LEFT JOIN (
                          SELECT
                              complex_groupcond.complex_id,
                              ROW_NUMBER() OVER (PARTITION BY complex_groupcond.complex_id ORDER BY complex_groupcond.cond) AS num
                          FROM complex_groupcond
                      ) complex_cond
                        ON complex_cond.complex_id = complex_in.complex_id
-- WHERE values.step = 1

                ) outereq
                GROUP BY
                    outereq.value_id,
                    outereq.name,
                    outereq.grp_cond


QUERY PLAN


HashAggregate  (cost=37101.37..37241.48 rows=14011 width=51) (actual time=1063.068..1116.203 rows=50000 loops=1)

  Group Key: "values".value_id, "values".name, (string_agg((jsonb_path_query_first(("values".value)::jsonb, ((('$['::text || ((complex_cond.num - 1))::text) || ']'::text))::jsonpath, '{}'::jsonb, false))::text, '_'::text) OVER (?))

  Batches: 5  Memory Usage: 4145kB  Disk Usage: 7728kB

  ->  Unique  (cost=1.92..34649.41 rows=140112 width=64) (actual time=3.441..924.772 rows=260000 loops=1)

        ->  WindowAgg  (cost=1.92..32691.07 rows=261113 width=64) (actual time=3.438..852.363 rows=280000 loops=1)

              ->  Incremental Sort  (cost=1.92..18982.63 rows=261113 width=76) (actual time=0.105..290.287 rows=280000 loops=1)

                    Sort Key: "values".name, "values".value_id, complex_in.complex_id

                    Presorted Key: "values".name, "values".value_id

                    Full-sort Groups: 7917  Sort Method: quicksort  Average Memory: 32kB  Peak Memory: 32kB

                    ->  Merge Join  (cost=1.63..8578.21 rows=261113 width=76) (actual time=0.057..102.297 rows=280000 loops=1)

                          Merge Cond: (("values".name)::text = (complex_in.input)::text)

                          ->  Index Scan using values_pkey on "values"  (cost=0.41..4521.22 rows=50000 width=55) (actual time=0.011..29.658 rows=50000 loops=1)

                          ->  Materialize  (cost=1.22..15.36 rows=26 width=26) (actual time=0.041..15.844 rows=270001 loops=1)

                                ->  Nested Loop Left Join  (cost=1.22..15.30 rows=26 width=26) (actual time=0.039..0.100 rows=28 loops=1)

                                      Join Filter: ((complex_cond.complex_id)::text = (complex_in.complex_id)::text)

                                      Rows Removed by Join Filter: 100

                                      ->  Index Only Scan using complex_in_pkey on complex_in  (cost=0.14..12.53 rows=26 width=18) (actual time=0.003..0.028 rows=26 loops=1)

                                            Heap Fetches: 26

                                      ->  Materialize  (cost=1.08..1.22 rows=4 width=25) (actual time=0.001..0.002 rows=4 loops=26)

                                            ->  Subquery Scan on complex_cond  (cost=1.08..1.20 rows=4 width=25) (actual time=0.032..0.038 rows=4 loops=1)

                                                  ->  WindowAgg  (cost=1.08..1.16 rows=4 width=30) (actual time=0.032..0.036 rows=4 loops=1)

                                                        ->  Sort  (cost=1.08..1.09 rows=4 width=22) (actual time=0.018..0.020 rows=4 loops=1)

                                                              Sort Key: complex_groupcond.complex_id, complex_groupcond.cond

                                                              Sort Method: quicksort  Memory: 25kB

                                                              ->  Seq Scan on complex_groupcond  (cost=0.00..1.04 rows=4 width=22) (actual time=0.009..0.010 rows=4 loops=1)

Planning Time: 1.025 ms

Execution Time: 1119.345 ms



EXPLAIN
schemaname	tablename	indexname	tablespace	indexdef
public	complex	complex_pkey	null	CREATE UNIQUE INDEX complex_pkey ON public.complex USING btree (complex_id)
public	complex_in	complex_in_pkey	null	CREATE UNIQUE INDEX complex_in_pkey ON public.complex_in USING btree (input, complex_id)
public	complex_groupcond	complex_groupcond_pkey	null	CREATE UNIQUE INDEX complex_groupcond_pkey ON public.complex_groupcond USING btree (cond, complex_id)