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?.
select version();


version


PostgreSQL 12.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


--
--
--  Method 1 - RECURSIVE CTE (two methods - 2-in-1 RCTE & GENERATE_SUBSCRIPS())
--
--
--
-- Setup of parameters...
--

CREATE TABLE param(n INT, m INT);
INSERT INTO param (VALUES(3, 5));
SELECT * FROM param;
1 rows affected



n
m


3
5


--
-- Step 1
--

WITH RECURSIVE cte_fill_array AS -- (cnt, val_arr, cnt_arr) AS
(
  SELECT 1 AS i, 1 AS cnt, ARRAY[1] AS val_arr, ARRAY[1] AS cnt_arr
  UNION ALL
  SELECT i + 1, cnt + 1, 
  ARRAY_APPEND
  (
    val_arr,
    (
      SELECT 
        CASE
          WHEN cnt < (SELECT n FROM param) THEN (i + 1)
          WHEN cnt = (SELECT n FROM param) THEN cnt
          WHEN cnt > (SELECT n FROM param) THEN 6 - i
        END
    )
  ),
  ARRAY_APPEND(cnt_arr, cnt + 1)
  FROM cte_fill_array
  WHERE cnt < 2 * (SELECT n FROM param)
)
SELECT i, cnt, val_arr, cnt_arr FROM cte_fill_array;


i
cnt
val_arr
cnt_arr


1
1
{1}
{1}

2
2
{1,2}
{1,2}

3
3
{1,2,3}
{1,2,3}

4
4
{1,2,3,3}
{1,2,3,4}

5
5
{1,2,3,3,2}
{1,2,3,4,5}

6
6
{1,2,3,3,2,1}
{1,2,3,4,5,6}


--
--  Step2 - end
--

WITH RECURSIVE cte_fill_arr (n, f_arr) AS
(
  SELECT 1 AS n, ARRAY[1]::INT[] AS f_arr
  UNION ALL
  SELECT n + 1, array_append(f_arr, n + 1)
  FROM cte_fill_arr
  WHERE n < (SELECT n * 2 FROM param)
)
SELECT arr AS the_series FROM
(
  SELECT 
    ROW_NUMBER() OVER () AS rn,
    UNNEST
    (
      (
        SELECT f_arr FROM cte_fill_arr
        WHERE CARDINALITY(f_arr) = (SELECT n * 2 FROM param)
      )
    ) AS seq,  
    UNNEST
    (
      ARRAY
      (
        SELECT 
          GENERATE_SERIES(1, (SELECT n FROM param))
        UNION ALL
        SELECT 
          GENERATE_SERIES((SELECT n FROM param), 1, -1)
      )
    ) AS arr
  FROM
    GENERATE_SERIES(1, (SELECT m FROM param)) AS x
  ORDER BY rn, seq
) AS fin_arr
ORDER BY rn, seq;



the_series


1

2

3

3

2

1

1

2

3

3

2

1

1

2

3

3

2

1

1

2

3

3

2

1

1

2

3

3

2

1


--
-- GENERATE_SUBSCRIPTs method...
--

WITH RECURSIVE cte (i) AS
(
  SELECT 1 AS i, 1 AS cnt
  UNION ALL
  SELECT 
    CASE
      WHEN cnt < (SELECT n FROM param) THEN (i + 1)
      WHEN cnt = (SELECT n FROM param) THEN cnt
      ELSE i - 1
    END AS i,
    cnt + 1
  FROM cte
  WHERE cnt < 2 * (SELECT n FROM param)
)
SELECT 
  x, 
  UNNEST(ARRAY(SELECT i FROM cte))    AS the_arr, 
  GENERATE_SUBSCRIPTS(ARRAY(SELECT i FROM cte), 1) AS ss
FROM 
  GENERATE_SERIES(1, (SELECT m FROM param)) AS t(x)
ORDER BY x, ss;


x
the_arr
ss


1
1
1

1
2
2

1
3
3

1
3
4

1
2
5

1
1
6

2
1
1

2
2
2

2
3
3

2
3
4

2
2
5

2
1
6

3
1
1

3
2
2

3
3
3

3
3
4

3
2
5

3
1
6

4
1
1

4
2
2

4
3
3

4
3
4

4
2
5

4
1
6

5
1
1

5
2
2

5
3
3

5
3
4

5
2
5

5
1
6


WITH RECURSIVE cte (i) AS
(
  SELECT 1 AS i, 1 AS cnt
  UNION ALL
  SELECT 
    CASE
      WHEN cnt < (SELECT n FROM param) THEN (i + 1)
      WHEN cnt = (SELECT n FROM param) THEN cnt
      ELSE i - 1
    END AS i,
    cnt + 1
  FROM cte
  WHERE cnt < 2 * (SELECT n FROM param)
)
SELECT the_arr AS the_series
FROM
(
  SELECT 
    x, 
    UNNEST(ARRAY(SELECT i FROM cte))    AS the_arr, 
    GENERATE_SUBSCRIPTS(ARRAY(SELECT i FROM cte), 1) AS ss

  FROM GENERATE_SERIES(1, (SELECT m FROM param)) AS t(x)
) AS s
ORDER BY x, ss;


the_series


1

2

3

3

2

1

1

2

3

3

2

1

1

2

3

3

2

1

1

2

3

3

2

1

1

2

3

3

2

1


EXPLAIN (ANALYZE, BUFFERS, VERBOSE) -- ~ .190 ms
WITH RECURSIVE cte_fill_arr (n, f_arr) AS
(
  SELECT 1 AS n, ARRAY[1]::INT[] AS f_arr
  UNION ALL
  SELECT n + 1, array_append(f_arr, n + 1)
  FROM cte_fill_arr
  WHERE n < (SELECT n * 2 FROM param)
)
SELECT arr AS the_series FROM
(
  SELECT 
    ROW_NUMBER() OVER () AS rn,
    UNNEST
    (
      (
        SELECT f_arr FROM cte_fill_arr
        WHERE CARDINALITY(f_arr) = (SELECT n * 2 FROM param)
      )
    ) AS seq,  
    UNNEST
    (
      ARRAY
      (
        SELECT 
          GENERATE_SERIES(1, (SELECT n FROM param))
        UNION ALL
        SELECT 
          GENERATE_SERIES((SELECT n FROM param), 1, -1)
      )
    ) AS arr
  FROM
    GENERATE_SERIES(1, (SELECT m FROM param)) AS x
  ORDER BY rn, seq
) AS fin_arr
ORDER BY rn, seq;


QUERY PLAN


Subquery Scan on fin_arr  (cost=1309.35..1434.35 rows=10000 width=16) (actual time=0.131..0.139 rows=30 loops=1)

  Output: fin_arr.arr, fin_arr.rn, fin_arr.seq

  Buffers: shared hit=5

  CTE cte_fill_arr

    ->  Recursive Union  (cost=0.00..385.61 rows=31 width=36) (actual time=0.001..0.027 rows=6 loops=1)

          Buffers: shared hit=1

          ->  Result  (cost=0.00..0.01 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=1)

                Output: 1, '{1}'::integer[]

          ->  WorkTable Scan on cte_fill_arr  (cost=38.25..38.50 rows=3 width=36) (actual time=0.003..0.003 rows=1 loops=6)

                Output: (cte_fill_arr.n + 1), array_append(cte_fill_arr.f_arr, (cte_fill_arr.n + 1))

                Filter: (cte_fill_arr.n < $1)

                Rows Removed by Filter: 0

                Buffers: shared hit=1

                InitPlan 1 (returns $1)

                  ->  Seq Scan on public.param  (cost=0.00..38.25 rows=2260 width=4) (actual time=0.005..0.005 rows=1 loops=1)

                        Output: (param.n * 2)

                        Buffers: shared hit=1

  ->  Sort  (cost=923.75..948.75 rows=10000 width=16) (actual time=0.130..0.133 rows=30 loops=1)

        Output: (row_number() OVER (?)), (unnest($4)), (unnest($7))

        Sort Key: (row_number() OVER (?)), (unnest($4))

        Sort Method: quicksort  Memory: 26kB

        Buffers: shared hit=5

        InitPlan 4 (returns $4)

          ->  CTE Scan on cte_fill_arr cte_fill_arr_1  (cost=38.25..39.02 rows=1 width=32) (actual time=0.037..0.038 rows=1 loops=1)

                Output: cte_fill_arr_1.f_arr

                Filter: (cardinality(cte_fill_arr_1.f_arr) = $3)

                Rows Removed by Filter: 5

                Buffers: shared hit=2

                InitPlan 3 (returns $3)

                  ->  Seq Scan on public.param param_1  (cost=0.00..38.25 rows=2260 width=4) (actual time=0.005..0.005 rows=1 loops=1)

                        Output: (param_1.n * 2)

                        Buffers: shared hit=1

        InitPlan 7 (returns $7)

          ->  Append  (cost=32.60..105.23 rows=2000 width=4) (actual time=0.011..0.021 rows=6 loops=1)

                Buffers: shared hit=2

                ->  ProjectSet  (cost=32.60..37.62 rows=1000 width=4) (actual time=0.011..0.012 rows=3 loops=1)

                      Output: generate_series(1, $5)

                      Buffers: shared hit=1

                      InitPlan 5 (returns $5)

                        ->  Seq Scan on public.param param_2  (cost=0.00..32.60 rows=2260 width=4) (actual time=0.005..0.005 rows=1 loops=1)

                              Output: param_2.n

                              Buffers: shared hit=1

                      ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)

                ->  ProjectSet  (cost=32.60..37.62 rows=1000 width=4) (actual time=0.006..0.007 rows=3 loops=1)

                      Output: generate_series($6, 1, '-1'::integer)

                      Buffers: shared hit=1

                      InitPlan 6 (returns $6)

                        ->  Seq Scan on public.param param_3  (cost=0.00..32.60 rows=2260 width=4) (actual time=0.004..0.005 rows=1 loops=1)

                              Output: param_3.n

                              Buffers: shared hit=1

                      ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)

        InitPlan 8 (returns $8)

          ->  Seq Scan on public.param param_4  (cost=0.00..32.60 rows=2260 width=4) (actual time=0.007..0.008 rows=1 loops=1)

                Output: param_4.m

                Buffers: shared hit=1

        ->  ProjectSet  (cost=0.00..82.50 rows=10000 width=16) (actual time=0.089..0.118 rows=30 loops=1)

              Output: (row_number() OVER (?)), unnest($4), unnest($7)

              Buffers: shared hit=5

              ->  WindowAgg  (cost=0.00..22.50 rows=1000 width=8) (actual time=0.027..0.048 rows=5 loops=1)

                    Output: row_number() OVER (?)

                    Buffers: shared hit=1

                    ->  Function Scan on pg_catalog.generate_series x  (cost=0.00..10.00 rows=1000 width=0) (actual time=0.021..0.022 rows=5 loops=1)

                          Function Call: generate_series(1, $8)

                          Buffers: shared hit=1

Planning Time: 0.240 ms

Execution Time: 0.198 ms


EXPLAIN (ANALYZE, BUFFERS, VERBOSE) -- ~ .110ms
WITH RECURSIVE cte (i) AS
(
  SELECT 1 AS i, 1 AS cnt
  UNION ALL
  SELECT 
    CASE
      WHEN cnt < (SELECT n FROM param) THEN (i + 1)
      WHEN cnt = (SELECT n FROM param) THEN cnt
      ELSE i - 1
    END AS i,
    cnt + 1
  FROM cte
  WHERE cnt < 2 * (SELECT n FROM param)
)
SELECT the_arr
FROM
(
  SELECT 
    x, 
    UNNEST(ARRAY(SELECT i FROM cte))    AS the_arr, 
    GENERATE_SUBSCRIPTS(ARRAY(SELECT i FROM cte), 1) AS ss

  FROM GENERATE_SERIES(1, (SELECT m FROM param)) AS t(x)
) AS s
ORDER BY x, ss;


QUERY PLAN


Sort  (cost=132783.69..135283.69 rows=1000000 width=12) (actual time=0.071..0.074 rows=30 loops=1)

  Output: s.the_arr, s.x, s.ss

  Sort Key: s.x, s.ss

  Sort Method: quicksort  Memory: 26kB

  Buffers: shared hit=4

  CTE cte

    ->  Recursive Union  (cost=0.00..981.50 rows=31 width=8) (actual time=0.001..0.029 rows=6 loops=1)

          Buffers: shared hit=3

          ->  Result  (cost=0.00..0.01 rows=1 width=8) (actual time=0.000..0.001 rows=1 loops=1)

                Output: 1, 1

          ->  WorkTable Scan on cte  (cost=97.80..98.09 rows=3 width=8) (actual time=0.004..0.004 rows=1 loops=6)

                Output: CASE WHEN (cte.cnt < $1) THEN (cte.i + 1) WHEN (cte.cnt = $2) THEN cte.cnt ELSE (cte.i - 1) END, (cte.cnt + 1)

                Filter: (cte.cnt < (2 * $3))

                Rows Removed by Filter: 0

                Buffers: shared hit=3

                InitPlan 1 (returns $1)

                  ->  Seq Scan on public.param  (cost=0.00..32.60 rows=2260 width=4) (actual time=0.004..0.004 rows=1 loops=1)

                        Output: param.n

                        Buffers: shared hit=1

                InitPlan 2 (returns $2)

                  ->  Seq Scan on public.param param_1  (cost=0.00..32.60 rows=2260 width=4) (actual time=0.004..0.004 rows=1 loops=1)

                        Output: param_1.n

                        Buffers: shared hit=1

                InitPlan 3 (returns $3)

                  ->  Seq Scan on public.param param_2  (cost=0.00..32.60 rows=2260 width=4) (actual time=0.005..0.005 rows=1 loops=1)

                        Output: param_2.n

                        Buffers: shared hit=1

  ->  Subquery Scan on s  (cost=33.84..15053.84 rows=1000000 width=12) (actual time=0.053..0.064 rows=30 loops=1)

        Output: s.the_arr, s.x, s.ss

        Buffers: shared hit=4

        ->  ProjectSet  (cost=33.84..5053.84 rows=1000000 width=12) (actual time=0.053..0.060 rows=30 loops=1)

              Output: t.x, unnest($5), generate_subscripts($6, 1)

              Buffers: shared hit=4

              InitPlan 5 (returns $5)

                ->  CTE Scan on cte cte_1  (cost=0.00..0.62 rows=31 width=4) (actual time=0.001..0.031 rows=6 loops=1)

                      Output: cte_1.i

                      Buffers: shared hit=3

              InitPlan 6 (returns $6)

                ->  CTE Scan on cte cte_2  (cost=0.00..0.62 rows=31 width=4) (actual time=0.000..0.001 rows=6 loops=1)

                      Output: cte_2.i

              InitPlan 7 (returns $7)

                ->  Seq Scan on public.param param_3  (cost=0.00..32.60 rows=2260 width=4) (actual time=0.009..0.009 rows=1 loops=1)

                      Output: param_3.m

                      Buffers: shared hit=1

              ->  Function Scan on pg_catalog.generate_series t  (cost=0.00..10.00 rows=1000 width=4) (actual time=0.015..0.016 rows=5 loops=1)

                    Output: t.x

                    Function Call: generate_series(1, $7)

                    Buffers: shared hit=1

Planning Time: 0.150 ms

Execution Time: 0.107 ms
i	cnt	val_arr	cnt_arr
1	1	{1}	{1}
2	2	{1,2}	{1,2}
3	3	{1,2,3}	{1,2,3}
4	4	{1,2,3,3}	{1,2,3,4}
5	5	{1,2,3,3,2}	{1,2,3,4,5}
6	6	{1,2,3,3,2,1}	{1,2,3,4,5,6}
x	the_arr	ss
1	1	1
1	2	2
1	3	3
1	3	4
1	2	5
1	1	6
2	1	1
2	2	2
2	3	3
2	3	4
2	2	5
2	1	6
3	1	1
3	2	2
3	3	3
3	3	4
3	2	5
3	1	6
4	1	1
4	2	2
4	3	3
4	3	4
4	2	5
4	1	6
5	1	1
5	2	2
5	3	3
5	3	4
5	2	5
5	1	6
x	the_arr	ss
1	1	1
1	2	2
1	3	3
1	3	4
1	2	5
1	1	6
2	1	1
2	2	2
2	3	3
2	3	4
2	2	5
2	1	6
3	1	1
3	2	2
3	3	3
3	3	4
3	2	5
3	1	6
4	1	1
4	2	2
4	3	3
4	3	4
4	2	5
4	1	6
5	1	1
5	2	2
5	3	3
5	3	4
5	2	5
5	1	6