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

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

n
m

3
5

CREATE TABLE numbers 
( n int not null primary key ) ;
INSERT INTO numbers (n)
SELECT GENERATE_SERIES(1, 1000);
1000 rows affected

SELECT 
    CASE WHEN g2.i = 1 THEN gn.i ELSE p.n + 1 - gn.i END AS xi
FROM
    parameters AS p
  JOIN numbers AS gn (i) ON gn.i <= p.n
  JOIN numbers AS g2 (i) ON g2.i <= 2
  JOIN numbers AS gm (i) ON gm.i <= p.m 
ORDER BY
    gm.i, g2.i, gn.i ;

xi

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

-- ypercube's query 1
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
WITH 
  parameters (n, m) AS
  ( VALUES (3, 5) )
SELECT 
    CASE WHEN g2.i = 1 THEN gn.i ELSE p.n + 1 - gn.i END AS xi
FROM
    parameters AS p, 
    generate_series(1, p.n) AS gn (i),
    generate_series(1, 2)   AS g2 (i),
    generate_series(1, p.m) AS gm (i)
ORDER BY
    gm.i, g2.i, gn.i ;

QUERY PLAN

Sort  (cost=1.47..1.55 rows=30 width=16) (actual time=0.051..0.053 rows=30 loops=1)

  Output: (CASE WHEN (g2.i = 1) THEN gn.i ELSE (4 - gn.i) END), gm.i, g2.i, gn.i

  Sort Key: gm.i, g2.i, gn.i

  Sort Method: quicksort  Memory: 26kB

  ->  Nested Loop  (cost=0.01..0.74 rows=30 width=16) (actual time=0.029..0.042 rows=30 loops=1)

        Output: CASE WHEN (g2.i = 1) THEN gn.i ELSE (4 - gn.i) END, gm.i, g2.i, gn.i

        ->  Function Scan on pg_catalog.generate_series gm  (cost=0.00..0.05 rows=5 width=4) (actual time=0.016..0.016 rows=5 loops=1)

              Output: gm.i

              Function Call: generate_series(1, 5)

        ->  Materialize  (cost=0.01..0.18 rows=6 width=8) (actual time=0.002..0.003 rows=6 loops=5)

              Output: gn.i, g2.i

              ->  Nested Loop  (cost=0.01..0.15 rows=6 width=8) (actual time=0.006..0.008 rows=6 loops=1)

                    Output: gn.i, g2.i

                    ->  Function Scan on pg_catalog.generate_series g2  (cost=0.00..0.02 rows=2 width=4) (actual time=0.003..0.003 rows=2 loops=1)

                          Output: g2.i

                          Function Call: generate_series(1, 2)

                    ->  Function Scan on pg_catalog.generate_series gn  (cost=0.00..0.03 rows=3 width=4) (actual time=0.001..0.001 rows=3 loops=2)

                          Output: gn.i

                          Function Call: generate_series(1, 3)

Planning Time: 0.071 ms

Execution Time: 0.103 ms

-- ypercube's query 1
-- using parameters table
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT 
    CASE WHEN g2.i = 1 THEN gn.i ELSE p.n + 1 - gn.i END AS xi
FROM
    parameters AS p, 
    generate_series(1, p.n) AS gn (i),
    generate_series(1, 2)   AS g2 (i),
    generate_series(1, p.m) AS gm (i)
ORDER BY
    gm.i, g2.i, gn.i ;

QUERY PLAN

Sort  (cost=1158239945.94..1169539945.94 rows=4520000000 width=16) (actual time=0.067..0.070 rows=30 loops=1)

  Output: (CASE WHEN (g2.i = 1) THEN gn.i ELSE ((p.n + 1) - gn.i) END), gm.i, g2.i, gn.i

  Sort Key: gm.i, g2.i, gn.i

  Sort Method: quicksort  Memory: 26kB

  Buffers: shared hit=1

  ->  Nested Loop  (cost=0.01..124390494.78 rows=4520000000 width=16) (actual time=0.023..0.045 rows=30 loops=1)

        Output: CASE WHEN (g2.i = 1) THEN gn.i ELSE ((p.n + 1) - gn.i) END, gm.i, g2.i, gn.i

        Buffers: shared hit=1

        ->  Nested Loop  (cost=0.01..90494.77 rows=4520000 width=16) (actual time=0.020..0.026 rows=6 loops=1)

              Output: p.n, p.m, gn.i, g2.i

              Buffers: shared hit=1

              ->  Nested Loop  (cost=0.00..94.77 rows=4520 width=12) (actual time=0.016..0.019 rows=2 loops=1)

                    Output: g2.i, p.n, p.m

                    Buffers: shared hit=1

                    ->  Function Scan on pg_catalog.generate_series g2  (cost=0.00..0.02 rows=2 width=4) (actual time=0.004..0.005 rows=2 loops=1)

                          Output: g2.i

                          Function Call: generate_series(1, 2)

                    ->  Materialize  (cost=0.00..43.90 rows=2260 width=8) (actual time=0.005..0.006 rows=1 loops=2)

                          Output: p.n, p.m

                          Buffers: shared hit=1

                          ->  Seq Scan on public.parameters p  (cost=0.00..32.60 rows=2260 width=8) (actual time=0.008..0.009 rows=1 loops=1)

                                Output: p.n, p.m

                                Buffers: shared hit=1

              ->  Function Scan on pg_catalog.generate_series gn  (cost=0.00..10.00 rows=1000 width=4) (actual time=0.002..0.002 rows=3 loops=2)

                    Output: gn.i

                    Function Call: generate_series(1, p.n)

        ->  Function Scan on pg_catalog.generate_series gm  (cost=0.00..10.00 rows=1000 width=4) (actual time=0.001..0.002 rows=5 loops=6)

              Output: gm.i

              Function Call: generate_series(1, p.m)

Planning Time: 0.082 ms

Execution Time: 0.098 ms

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) -- ~ 0.150 ms
-- ypercube's query 2
WITH 
  parameters (n, m) AS
  ( VALUES (3, 5) )
SELECT 
    CASE WHEN g2.i = 1 THEN gn.i ELSE p.n + 1 - gn.i END AS xi
FROM
    parameters AS p
  JOIN numbers AS gn (i) ON gn.i <= p.n
  JOIN numbers AS g2 (i) ON g2.i <= 2
  JOIN numbers AS gm (i) ON gm.i <= p.m 
ORDER BY
    gm.i, g2.i, gn.i ;

QUERY PLAN

Sort  (cost=142381235.98..143916548.48 rows=614125000 width=16) (actual time=0.059..0.062 rows=30 loops=1)

  Output: (CASE WHEN (g2.i = 1) THEN gn.i ELSE (4 - gn.i) END), gm.i, g2.i, gn.i

  Sort Key: gm.i, g2.i, gn.i

  Sort Method: quicksort  Memory: 26kB

  Buffers: shared hit=9

  ->  Nested Loop  (cost=44.60..10756329.48 rows=614125000 width=16) (actual time=0.033..0.048 rows=30 loops=1)

        Output: CASE WHEN (g2.i = 1) THEN gn.i ELSE (4 - gn.i) END, gm.i, g2.i, gn.i

        Buffers: shared hit=9

        ->  Nested Loop  (cost=29.73..9104.36 rows=722500 width=8) (actual time=0.014..0.017 rows=6 loops=1)

              Output: gn.i, g2.i

              Buffers: shared hit=6

              ->  Bitmap Heap Scan on public.numbers gn  (cost=14.87..35.49 rows=850 width=4) (actual time=0.008..0.009 rows=3 loops=1)

                    Output: gn.i

                    Recheck Cond: (gn.i <= 3)

                    Heap Blocks: exact=1

                    Buffers: shared hit=3

                    ->  Bitmap Index Scan on numbers_pkey  (cost=0.00..14.65 rows=850 width=0) (actual time=0.005..0.006 rows=3 loops=1)

                          Index Cond: (gn.i <= 3)

                          Buffers: shared hit=2

              ->  Materialize  (cost=14.87..39.74 rows=850 width=4) (actual time=0.001..0.002 rows=2 loops=3)

                    Output: g2.i

                    Buffers: shared hit=3

                    ->  Bitmap Heap Scan on public.numbers g2  (cost=14.87..35.49 rows=850 width=4) (actual time=0.002..0.002 rows=2 loops=1)

                          Output: g2.i

                          Recheck Cond: (g2.i <= 2)

                          Heap Blocks: exact=1

                          Buffers: shared hit=3

                          ->  Bitmap Index Scan on numbers_pkey  (cost=0.00..14.65 rows=850 width=0) (actual time=0.001..0.001 rows=2 loops=1)

                                Index Cond: (g2.i <= 2)

                                Buffers: shared hit=2

        ->  Materialize  (cost=14.87..39.74 rows=850 width=4) (actual time=0.003..0.004 rows=5 loops=6)

              Output: gm.i

              Buffers: shared hit=3

              ->  Bitmap Heap Scan on public.numbers gm  (cost=14.87..35.49 rows=850 width=4) (actual time=0.005..0.006 rows=5 loops=1)

                    Output: gm.i

                    Recheck Cond: (gm.i <= 5)

                    Heap Blocks: exact=1

                    Buffers: shared hit=3

                    ->  Bitmap Index Scan on numbers_pkey  (cost=0.00..14.65 rows=850 width=0) (actual time=0.003..0.003 rows=5 loops=1)

                          Index Cond: (gm.i <= 5)

                          Buffers: shared hit=2

Planning Time: 0.123 ms

Execution Time: 0.159 ms

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) -- ~ 
-- ypercube's query 2
-- using parameters table
SELECT 
    CASE WHEN g2.i = 1 THEN gn.i ELSE p.n + 1 - gn.i END AS xi
FROM
    parameters AS p
  JOIN numbers AS gn (i) ON gn.i <= p.n
  JOIN numbers AS g2 (i) ON g2.i <= 2
  JOIN numbers AS gm (i) ON gm.i <= p.m 
ORDER BY
    gm.i, g2.i, gn.i ;

QUERY PLAN

Sort  (cost=473756301482.78..477226107732.78 rows=1387922500000 width=16) (actual time=0.118..0.121 rows=30 loops=1)

  Output: (CASE WHEN (g2.i = 1) THEN gn.i ELSE ((p.n + 1) - gn.i) END), gm.i, g2.i, gn.i

  Sort Key: gm.i, g2.i, gn.i

  Sort Method: quicksort  Memory: 26kB

  Buffers: shared hit=16

  ->  Nested Loop  (cost=15.43..27803995845.52 rows=1387922500000 width=16) (actual time=0.078..0.105 rows=30 loops=1)

        Output: CASE WHEN (g2.i = 1) THEN gn.i ELSE ((p.n + 1) - gn.i) END, gm.i, g2.i, gn.i

        Buffers: shared hit=16

        ->  Nested Loop  (cost=0.56..45545807.90 rows=1632850000 width=12) (actual time=0.047..0.061 rows=15 loops=1)

              Output: p.n, gn.i, gm.i

              Buffers: shared hit=13

              ->  Nested Loop  (cost=0.28..53552.90 rows=1921000 width=12) (actual time=0.026..0.028 rows=3 loops=1)

                    Output: p.n, p.m, gn.i

                    Buffers: shared hit=4

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

                          Output: p.n, p.m

                          Buffers: shared hit=1

                    ->  Index Only Scan using numbers_pkey on public.numbers gn  (cost=0.28..15.18 rows=850 width=4) (actual time=0.016..0.017 rows=3 loops=1)

                          Output: gn.i

                          Index Cond: (gn.i <= p.n)

                          Heap Fetches: 3

                          Buffers: shared hit=3

              ->  Index Only Scan using numbers_pkey on public.numbers gm  (cost=0.28..15.18 rows=850 width=4) (actual time=0.007..0.009 rows=5 loops=3)

                    Output: gm.i

                    Index Cond: (gm.i <= p.m)

                    Heap Fetches: 15

                    Buffers: shared hit=9

        ->  Materialize  (cost=14.87..39.74 rows=850 width=4) (actual time=0.002..0.002 rows=2 loops=15)

              Output: g2.i

              Buffers: shared hit=3

              ->  Bitmap Heap Scan on public.numbers g2  (cost=14.87..35.49 rows=850 width=4) (actual time=0.011..0.011 rows=2 loops=1)

                    Output: g2.i

                    Recheck Cond: (g2.i <= 2)

                    Heap Blocks: exact=1

                    Buffers: shared hit=3

                    ->  Bitmap Index Scan on numbers_pkey  (cost=0.00..14.65 rows=850 width=0) (actual time=0.004..0.004 rows=2 loops=1)

                          Index Cond: (g2.i <= 2)

                          Buffers: shared hit=2

Planning Time: 0.145 ms

Execution Time: 0.163 ms