Postgres 13

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 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


--
-- Erwin Brandstetter's solutions
--
CREATE TABLE parameters (n INT, m INT);
INSERT INTO parameters (VALUES (3, 5)); SELECT * FROM parameters;
1 rows affected



n
m


3
5


CREATE OR REPLACE FUNCTION generate_up_down_series(n int, m int)
  RETURNS SETOF int
  LANGUAGE sql IMMUTABLE AS
$func$
SELECT 
  CASE 
    WHEN n2 < n THEN n2 + 1 
    ELSE n*2 - n2 
  END
FROM  
(
  SELECT n2m, n2m % (n*2) AS n2
  FROM   generate_series(0, n*2*m - 1) n2m
) sub
ORDER  BY n2m
$func$;
SELECT 
  GENERATE_UP_DOWN_SERIES(3, 5) AS i;


i


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


SELECT *, 
  CASE 
    WHEN n2 < 3 THEN n2 + 1 
    ELSE 3 * 2 - n2 
  END
FROM  
(
  SELECT n2m, n2m % (3 * 2) AS n2
  FROM   generate_series(0, 3 * 2 * 5 - 1) n2m
) AS t


n2m
n2
case


0
0
1

1
1
2

2
2
3

3
3
3

4
4
2

5
5
1

6
0
1

7
1
2

8
2
3

9
3
3

10
4
2

11
5
1

12
0
1

13
1
2

14
2
3

15
3
3

16
4
2

17
5
1

18
0
1

19
1
2

20
2
3

21
3
3

22
4
2

23
5
1

24
0
1

25
1
2

26
2
3

27
3
3

28
4
2

29
5
1


EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT 
  GENERATE_UP_DOWN_SERIES(3, 5) AS i;


QUERY PLAN


ProjectSet  (cost=0.00..5.27 rows=1000 width=4) (actual time=0.126..0.140 rows=30 loops=1)

  Output: generate_up_down_series(3, 5)

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

Planning Time: 0.010 ms

Execution Time: 0.150 ms


CREATE TABLE int_seq (i integer);

WITH RECURSIVE cte(i) AS (
   SELECT 0
   UNION ALL
   SELECT i+1 FROM cte
   WHERE  i < 20000  -- or as many you might need!
   )
INSERT INTO int_seq
SELECT i FROM cte;
20001 rows affected

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


WITH RECURSIVE cte(i) AS 
(
  SELECT 0
  UNION ALL
  SELECT i+1 FROM cte
  WHERE  i < 20000  -- or as many you might need!
)
SELECT 
  CASE 
    WHEN n2 < (SELECT n FROM param) THEN n2 + 1 
    ELSE ((SELECT n FROM param) * 2) - n2 
  END AS x
FROM  (
   SELECT i, i % ((SELECT n FROM param) * 2) AS n2
   FROM   cte
   WHERE  i < (SELECT n FROM param) * 2 * (SELECT m FROM param)  -- remember: 0 to N-1
   ) sub
ORDER  BY i;


x


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)
WITH RECURSIVE cte(i) AS 
(
  SELECT 0
  UNION ALL
  SELECT i+1 FROM cte
  WHERE  i < 20000  -- or as many you might need!
)
SELECT 
  CASE 
    WHEN n2 < (SELECT n FROM param) THEN n2 + 1 
    ELSE ((SELECT n FROM param) * 2) - n2 
  END AS x
FROM  (
   SELECT i, i % ((SELECT n FROM param) * 2) AS n2
   FROM   cte
   WHERE  i < (SELECT n FROM param) * 2 * (SELECT m FROM param)  -- remember: 0 to N-1
   ) sub
ORDER  BY i;


QUERY PLAN


Sort  (cost=232.42..232.45 rows=10 width=8) (actual time=20.023..20.030 rows=30 loops=1)

  Output: (CASE WHEN ((cte.i % ($2 * 2)) < $3) THEN ((cte.i % ($4 * 2)) + 1) ELSE (($5 * 2) - (cte.i % ($6 * 2))) END), cte.i

  Sort Key: cte.i

  Sort Method: quicksort  Memory: 26kB

  Buffers: shared hit=7

  CTE cte

    ->  Recursive Union  (cost=0.00..2.95 rows=31 width=4) (actual time=0.003..12.957 rows=20001 loops=1)

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

                Output: 0

          ->  WorkTable Scan on cte cte_1  (cost=0.00..0.23 rows=3 width=4) (actual time=0.000..0.000 rows=1 loops=20001)

                Output: (cte_1.i + 1)

                Filter: (cte_1.i < 20000)

                Rows Removed by Filter: 0

  InitPlan 2 (returns $2)

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

          Output: param.n

          Buffers: shared hit=1

  InitPlan 3 (returns $3)

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

          Output: param_1.n

          Buffers: shared hit=1

  InitPlan 4 (returns $4)

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

          Output: param_2.n

          Buffers: shared hit=1

  InitPlan 5 (returns $5)

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

  InitPlan 6 (returns $6)

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

          Output: param_4.n

          Buffers: shared hit=1

  InitPlan 7 (returns $7)

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

          Output: param_5.n

          Buffers: shared hit=1

  InitPlan 8 (returns $8)

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

          Output: param_6.m

          Buffers: shared hit=1

  ->  CTE Scan on cte  (cost=0.00..1.10 rows=10 width=8) (actual time=0.037..20.012 rows=30 loops=1)

        Output: CASE WHEN ((cte.i % ($2 * 2)) < $3) THEN ((cte.i % ($4 * 2)) + 1) ELSE (($5 * 2) - (cte.i % ($6 * 2))) END, cte.i

        Filter: (cte.i < (($7 * 2) * $8))

        Rows Removed by Filter: 19971

        Buffers: shared hit=7

Planning Time: 0.438 ms

Execution Time: 20.431 ms
n2m	n2	case
0	0	1
1	1	2
2	2	3
3	3	3
4	4	2
5	5	1
6	0	1
7	1	2
8	2	3
9	3	3
10	4	2
11	5	1
12	0	1
13	1	2
14	2	3
15	3	3
16	4	2
17	5	1
18	0	1
19	1	2
20	2	3
21	3	3
22	4	2
23	5	1
24	0	1
25	1	2
26	2	3
27	3	3
28	4	2
29	5	1
n2m	n2	case
0	0	1
1	1	2
2	2	3
3	3	3
4	4	2
5	5	1
6	0	1
7	1	2
8	2	3
9	3	3
10	4	2
11	5	1
12	0	1
13	1	2
14	2	3
15	3	3
16	4	2
17	5	1
18	0	1
19	1	2
20	2	3
21	3	3
22	4	2
23	5	1
24	0	1
25	1	2
26	2	3
27	3	3
28	4	2
29	5	1