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


--
-- Abelisto's queries
--
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 
  s(i,n,z) as (
    select * 
    from 
      (values (1,1,1), ((SELECT n FROM param) * 2, 1, 2)) as v  -- Here 3 is n
    union all
    select
      case z when 1 then i+1 when 2 then i-1 end, 
      n+1,
      z 
    from s 
    where n < (SELECT n FROM param)), -- And here 3 is n
  m(m) as (select 1 union all select m+1 from m where m < (SELECT m FROM param)) -- Here 5 is m
select *, n from s, m order by m, i;


i
n
z
m
n


1
1
1
1
1

2
2
1
1
2

3
3
1
1
3

4
3
2
1
3

5
2
2
1
2

6
1
2
1
1

1
1
1
2
1

2
2
1
2
2

3
3
1
2
3

4
3
2
2
3

5
2
2
2
2

6
1
2
2
1

1
1
1
3
1

2
2
1
3
2

3
3
1
3
3

4
3
2
3
3

5
2
2
3
2

6
1
2
3
1

1
1
1
4
1

2
2
1
4
2

3
3
1
4
3

4
3
2
4
3

5
2
2
4
2

6
1
2
4
1

1
1
1
5
1

2
2
1
5
2

3
3
1
5
3

4
3
2
5
3

5
2
2
5
2

6
1
2
5
1


EXPLAIN (ANALYZE, BUFFERS, VERBOSE) -- ~ 0.145 ms
with recursive 
  s(i,n,z) as (
    select * 
    from 
      (values (1,1,1), ((SELECT n FROM param) * 2, 1, 2)) as v  -- Here 3 is n
    union all
    select
      case z when 1 then i+1 when 2 then i-1 end, 
      n+1,
      z 
    from s 
    where n < (SELECT n FROM param)), -- And here 3 is n
  m(m) as (select 1 union all select m+1 from m where m < (SELECT m FROM param)) -- Here 5 is m
select *, n from s, m order by m, i;


QUERY PLAN


Sort  (cost=864.52..870.10 rows=2232 width=20) (actual time=0.091..0.094 rows=30 loops=1)

  Output: s.i, s.n, s.z, m.m, s.n

  Sort Key: m.m, s.i

  Sort Method: quicksort  Memory: 27kB

  Buffers: shared hit=3

  CTE s

    ->  Recursive Union  (cost=32.60..365.44 rows=72 width=12) (actual time=0.005..0.042 rows=6 loops=1)

          Buffers: shared hit=2

          ->  Values Scan on "*VALUES*"  (cost=32.60..32.62 rows=2 width=12) (actual time=0.005..0.016 rows=2 loops=1)

                Output: "*VALUES*".column1, "*VALUES*".column2, "*VALUES*".column3

                Buffers: shared hit=1

                InitPlan 1 (returns $1)

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

                        Output: param.n

                        Buffers: shared hit=1

          ->  WorkTable Scan on s s_1  (cost=32.60..33.14 rows=7 width=12) (actual time=0.006..0.006 rows=1 loops=3)

                Output: CASE s_1.z WHEN 1 THEN (s_1.i + 1) WHEN 2 THEN (s_1.i - 1) ELSE NULL::integer END, (s_1.n + 1), s_1.z

                Filter: (s_1.n < $2)

                Rows Removed by Filter: 1

                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.005..0.005 rows=1 loops=1)

                        Output: param_1.n

                        Buffers: shared hit=1

  CTE m

    ->  Recursive Union  (cost=0.00..328.96 rows=31 width=4) (actual time=0.002..0.019 rows=5 loops=1)

          Buffers: shared hit=1

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

                Output: 1

          ->  WorkTable Scan on m m_1  (cost=32.60..32.83 rows=3 width=4) (actual time=0.003..0.003 rows=1 loops=5)

                Output: (m_1.m + 1)

                Filter: (m_1.m < $5)

                Rows Removed by Filter: 0

                Buffers: shared hit=1

                InitPlan 4 (returns $5)

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

                        Output: param_2.m

                        Buffers: shared hit=1

  ->  Nested Loop  (cost=0.00..45.98 rows=2232 width=20) (actual time=0.010..0.076 rows=30 loops=1)

        Output: s.i, s.n, s.z, m.m, s.n

        Buffers: shared hit=3

        ->  CTE Scan on m  (cost=0.00..0.62 rows=31 width=4) (actual time=0.003..0.021 rows=5 loops=1)

              Output: m.m

              Buffers: shared hit=1

        ->  CTE Scan on s  (cost=0.00..1.44 rows=72 width=12) (actual time=0.001..0.009 rows=6 loops=5)

              Output: s.i, s.n, s.z

              Buffers: shared hit=2

Planning Time: 0.217 ms

Execution Time: 0.142 ms


with recursive s(i,n,z) as (
  select * from (values(1,1,1),(3*2,1,2)) as v
  union all
  select
    case z when 1 then i+1 when 2 then i-1 end, 
    n+1,
    z 
  from s where n<3
)
select * from s order by i;


i
n
z


1
1
1

2
2
1

3
3
1

4
3
2

5
2
2

6
1
2


with recursive s(i,n,z) as (
  select * from (values(1,1,1),(1,1,1)) as v
  union all
  select
    i,
    n+1,
    z 
  from s where n<3
)
select * from s;


i
n
z


1
1
1

1
1
1

1
2
1

1
2
1

1
3
1

1
3
1
i	n	z	m	n
1	1	1	1	1
2	2	1	1	2
3	3	1	1	3
4	3	2	1	3
5	2	2	1	2
6	1	2	1	1
1	1	1	2	1
2	2	1	2	2
3	3	1	2	3
4	3	2	2	3
5	2	2	2	2
6	1	2	2	1
1	1	1	3	1
2	2	1	3	2
3	3	1	3	3
4	3	2	3	3
5	2	2	3	2
6	1	2	3	1
1	1	1	4	1
2	2	1	4	2
3	3	1	4	3
4	3	2	4	3
5	2	2	4	2
6	1	2	4	1
1	1	1	5	1
2	2	1	5	2
3	3	1	5	3
4	3	2	5	3
5	2	2	5	2
6	1	2	5	1
i	n	z	m	n
1	1	1	1	1
2	2	1	1	2
3	3	1	1	3
4	3	2	1	3
5	2	2	1	2
6	1	2	1	1
1	1	1	2	1
2	2	1	2	2
3	3	1	2	3
4	3	2	2	3
5	2	2	2	2
6	1	2	2	1
1	1	1	3	1
2	2	1	3	2
3	3	1	3	3
4	3	2	3	3
5	2	2	3	2
6	1	2	3	1
1	1	1	4	1
2	2	1	4	2
3	3	1	4	3
4	3	2	4	3
5	2	2	4	2
6	1	2	4	1
1	1	1	5	1
2	2	1	5	2
3	3	1	5	3
4	3	2	5	3
5	2	2	5	2
6	1	2	5	1