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 - GENERATE_SERIES()
--
--
--
-- 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
--

SELECT
  GENERATE_SERIES(1, (SELECT n FROM param)) AS the_first_series
UNION ALL
SELECT 
  GENERATE_SERIES((SELECT n FROM param), 1, -1);


the_first_series


1

2

3

3

2

1


--
--  Two possible Step 2s using a PL/pgSQL function or an RCTE
--

CREATE OR REPLACE FUNCTION fill_array_with_seq(the_array anyarray, seq_num INT)
RETURNS ANYARRAY LANGUAGE PLpgSQL AS $$
DECLARE
BEGIN
  FOR i IN 1..seq_num LOOP
    the_array[i] := i;
    i = i + 1;
  END LOOP;
  RETURN the_array;
end $$;

SELECT fill_array_with_seq(ARRAY[]::INT[], (SELECT n * 2 FROM param));

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 f_arr FROM cte_fill_arr
WHERE CARDINALITY(f_arr) = (SELECT n * 2 FROM param);


fill_array_with_seq


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




f_arr


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


--
-- Step 3
--

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 
  ROW_NUMBER() OVER () AS rn,
  (
    SELECT f_arr FROM cte_fill_arr
    WHERE CARDINALITY(f_arr) = (SELECT n * 2 FROM param)
  ),
  
  -- could use
  --
  -- fill_array_with_seq(ARRAY[]::INT[], (SELECT n * 2 FROM param))
  --
  
  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;


rn
f_arr
arr


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

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

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

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

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


--
--  Steps 4 & 5 - separate subquery not shown
--

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 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 the_series
  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


EXPLAIN (ANALYZE, BUFFERS, VERBOSE) -- ~ 0.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 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 the_series
  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.111..0.119 rows=30 loops=1)

  Output: fin_arr.the_series, 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.026 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.004..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.110..0.113 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.035..0.037 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.007..0.016 rows=6 loops=1)

                Buffers: shared hit=2

                ->  ProjectSet  (cost=32.60..37.62 rows=1000 width=4) (actual time=0.007..0.008 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.005..0.006 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.004 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.081..0.098 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.025..0.034 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.014..0.015 rows=5 loops=1)

                          Function Call: generate_series(1, $8)

                          Buffers: shared hit=1

Planning Time: 0.296 ms

Execution Time: 0.193 ms
rn	f_arr	arr
1	{1,2,3,4,5,6}	{1,2,3,3,2,1}
2	{1,2,3,4,5,6}	{1,2,3,3,2,1}
3	{1,2,3,4,5,6}	{1,2,3,3,2,1}
4	{1,2,3,4,5,6}	{1,2,3,3,2,1}
5	{1,2,3,4,5,6}	{1,2,3,3,2,1}