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

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

n
m

340
570

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

-- ypercube's query 1
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
WITH 
  parameters (n, m) AS
  ( VALUES (340, 570) )
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=49406.96..50375.96 rows=387600 width=16) (actual time=203.797..254.839 rows=387600 loops=1)

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

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

  Sort Method: external merge  Disk: 9888kB

  Buffers: temp read=1236 written=1242

  ->  Nested Loop  (cost=0.01..6804.03 rows=387600 width=16) (actual time=0.134..98.203 rows=387600 loops=1)

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

        ->  Function Scan on pg_catalog.generate_series gm  (cost=0.00..5.70 rows=570 width=4) (actual time=0.068..0.180 rows=570 loops=1)

              Output: gm.i

              Function Call: generate_series(1, 570)

        ->  Materialize  (cost=0.01..17.02 rows=680 width=8) (actual time=0.000..0.056 rows=680 loops=570)

              Output: gn.i, g2.i

              ->  Nested Loop  (cost=0.01..13.63 rows=680 width=8) (actual time=0.050..0.199 rows=680 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.006..0.007 rows=2 loops=1)

                          Output: g2.i

                          Function Call: generate_series(1, 2)

                    ->  Function Scan on pg_catalog.generate_series gn  (cost=0.00..3.40 rows=340 width=4) (actual time=0.021..0.047 rows=340 loops=2)

                          Output: gn.i

                          Function Call: generate_series(1, 340)

Planning:

  Buffers: shared hit=8 read=6

Planning Time: 0.269 ms

Execution Time: 293.894 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=407.393..479.200 rows=387600 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: external merge  Disk: 9888kB

  Buffers: shared hit=1, temp read=1236 written=1242

  ->  Nested Loop  (cost=0.01..124390494.78 rows=4520000000 width=16) (actual time=0.119..132.547 rows=387600 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.071..0.394 rows=680 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.028..0.038 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.006..0.007 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.010..0.012 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.010..0.014 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.033..0.092 rows=340 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.049..0.095 rows=570 loops=680)

              Output: gm.i

              Function Call: generate_series(1, p.m)

Planning:

  Buffers: shared hit=2

Planning Time: 0.175 ms

Execution Time: 518.700 ms

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) -- ~ 0.150 ms
-- ypercube's query 2
WITH 
  parameters (n, m) AS
  ( VALUES (340, 570) )
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

Incremental Sort  (cost=139839.45..127282288.11 rows=614125000 width=16) (actual time=0.894..376.536 rows=387600 loops=1)

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

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

  Presorted Key: gm.i

  Full-sort Groups: 570  Sort Method: quicksort  Average Memory: 28kB  Peak Memory: 28kB

  Pre-sorted Groups: 570  Sort Method: quicksort  Average Memory: 56kB  Peak Memory: 56kB

  Buffers: shared hit=13

  ->  Nested Loop  (cost=30.02..10756357.14 rows=614125000 width=16) (actual time=0.116..181.682 rows=387600 loops=1)

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

        Buffers: shared hit=13

        ->  Nested Loop  (cost=15.15..9132.02 rows=722500 width=8) (actual time=0.111..45.242 rows=193800 loops=1)

              Output: gn.i, gm.i

              Buffers: shared hit=10

              ->  Index Only Scan using numbers_pkey on public.numbers gm  (cost=0.28..63.16 rows=850 width=4) (actual time=0.067..1.372 rows=570 loops=1)

                    Output: gm.i

                    Index Cond: (gm.i <= 570)

                    Heap Fetches: 570

                    Buffers: shared hit=6

              ->  Materialize  (cost=14.87..39.74 rows=850 width=4) (actual time=0.000..0.025 rows=340 loops=570)

                    Output: gn.i

                    Buffers: shared hit=4

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

                          Output: gn.i

                          Recheck Cond: (gn.i <= 340)

                          Heap Blocks: exact=2

                          Buffers: shared hit=4

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

                                Index Cond: (gn.i <= 340)

                                Buffers: shared hit=2

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

              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.003..0.004 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.002 rows=2 loops=1)

                          Index Cond: (g2.i <= 2)

                          Buffers: shared hit=2

Planning:

  Buffers: shared hit=4 read=1

Planning Time: 0.276 ms

Execution Time: 414.977 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=473756334511.66..477226140761.66 rows=1387922500000 width=16) (actual time=492.546..544.079 rows=387600 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: external merge  Disk: 9888kB

  Buffers: shared hit=2293, temp read=1236 written=1242

  ->  Nested Loop  (cost=15.43..27804028874.40 rows=1387922500000 width=16) (actual time=0.074..230.339 rows=387600 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=2293

        ->  Nested Loop  (cost=0.56..45578836.78 rows=1632850000 width=12) (actual time=0.049..90.455 rows=193800 loops=1)

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

              Buffers: shared hit=2290

              ->  Nested Loop  (cost=0.28..86581.78 rows=1921000 width=8) (actual time=0.031..0.983 rows=570 loops=1)

                    Output: p.n, gm.i

                    Join Filter: (gm.i <= p.m)

                    Rows Removed by Join Filter: 430

                    Buffers: shared hit=10

                    ->  Index Only Scan using numbers_pkey on public.numbers gm  (cost=0.28..98.53 rows=2550 width=4) (actual time=0.018..0.412 rows=1000 loops=1)

                          Output: gm.i

                          Heap Fetches: 1000

                          Buffers: shared hit=9

                    ->  Materialize  (cost=0.00..43.90 rows=2260 width=8) (actual time=0.000..0.000 rows=1 loops=1000)

                          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

              ->  Index Only Scan using numbers_pkey on public.numbers gn  (cost=0.28..15.18 rows=850 width=4) (actual time=0.015..0.108 rows=340 loops=570)

                    Output: gn.i

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

                    Heap Fetches: 193800

                    Buffers: shared hit=2280

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

              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.005..0.007 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.003..0.003 rows=2 loops=1)

                          Index Cond: (g2.i <= 2)

                          Buffers: shared hit=2

Planning Time: 0.207 ms

Execution Time: 583.345 ms