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

select
  gn.i
from
  ( with g(i) as
    ( select * from generate_series(1, 3) )
    (select i from g)
    union all
    (select i from g order by i desc)
  ) as gn,
  generate_series(1, 5) as gm (j) ;

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

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
select
  gn.i
from
  ( with g(i) as
    ( select * from generate_series(1, 3) )
    (select i from g)
    union all
    (select i from g order by i desc)
  ) as gn,
  generate_series(1, 5) as gm (j) ;

QUERY PLAN

Nested Loop  (cost=0.04..0.75 rows=30 width=4) (actual time=0.017..0.032 rows=30 loops=1)

  Output: g.i

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

        Output: gm.j

        Function Call: generate_series(1, 5)

  ->  Materialize  (cost=0.03..0.33 rows=6 width=4) (actual time=0.001..0.003 rows=6 loops=5)

        Output: g.i

        ->  Append  (cost=0.03..0.24 rows=6 width=4) (actual time=0.003..0.009 rows=6 loops=1)

              CTE g

                ->  Function Scan on pg_catalog.generate_series  (cost=0.00..0.03 rows=3 width=4) (actual time=0.002..0.003 rows=3 loops=1)

                      Output: generate_series.generate_series

                      Function Call: generate_series(1, 3)

              ->  CTE Scan on g  (cost=0.00..0.06 rows=3 width=4) (actual time=0.003..0.004 rows=3 loops=1)

                    Output: g.i

              ->  Sort  (cost=0.08..0.09 rows=3 width=4) (actual time=0.004..0.004 rows=3 loops=1)

                    Output: g_1.i

                    Sort Key: g_1.i DESC

                    Sort Method: quicksort  Memory: 25kB

                    ->  CTE Scan on g g_1  (cost=0.00..0.06 rows=3 width=4) (actual time=0.000..0.001 rows=3 loops=1)

                          Output: g_1.i

Planning Time: 0.143 ms

Execution Time: 0.057 ms

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
select
  gn.i
from
  ( with g(i) as
    ( select * from generate_series(1, 34) )
    (select i from g)
    union all
    (select i from g order by i desc)
  ) as gn,
  generate_series(1, 57) as gm (j) ;

QUERY PLAN

Nested Loop  (cost=0.35..53.20 rows=3876 width=4) (actual time=0.012..0.784 rows=3876 loops=1)

  Output: g.i

  ->  Function Scan on pg_catalog.generate_series gm  (cost=0.00..0.57 rows=57 width=0) (actual time=0.007..0.011 rows=57 loops=1)

        Output: gm.j

        Function Call: generate_series(1, 57)

  ->  Materialize  (cost=0.34..4.35 rows=68 width=4) (actual time=0.000..0.005 rows=68 loops=57)

        Output: g.i

        ->  Append  (cost=0.34..3.33 rows=68 width=4) (actual time=0.004..0.032 rows=68 loops=1)

              CTE g

                ->  Function Scan on pg_catalog.generate_series  (cost=0.00..0.34 rows=34 width=4) (actual time=0.003..0.005 rows=34 loops=1)

                      Output: generate_series.generate_series

                      Function Call: generate_series(1, 34)

              ->  CTE Scan on g  (cost=0.00..0.68 rows=34 width=4) (actual time=0.003..0.012 rows=34 loops=1)

                    Output: g.i

              ->  Sort  (cost=1.54..1.63 rows=34 width=4) (actual time=0.011..0.013 rows=34 loops=1)

                    Output: g_1.i

                    Sort Key: g_1.i DESC

                    Sort Method: quicksort  Memory: 26kB

                    ->  CTE Scan on g g_1  (cost=0.00..0.68 rows=34 width=4) (actual time=0.000..0.003 rows=34 loops=1)

                          Output: g_1.i

Planning Time: 0.096 ms

Execution Time: 1.006 ms

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
select
  gn.i
from
  ( with g(i) as
    ( select * from generate_series(1, 340) )
    (select i from g)
    union all
    (select i from g order by i desc)
  ) as gn,
  generate_series(1, 570) as gm (j) ;

QUERY PLAN

Nested Loop  (cost=3.40..4898.15 rows=387600 width=4) (actual time=0.094..71.857 rows=387600 loops=1)

  Output: g.i

  ->  Function Scan on pg_catalog.generate_series gm  (cost=0.00..5.70 rows=570 width=0) (actual time=0.053..0.129 rows=570 loops=1)

        Output: gm.j

        Function Call: generate_series(1, 570)

  ->  Materialize  (cost=3.40..49.15 rows=680 width=4) (actual time=0.000..0.044 rows=680 loops=570)

        Output: g.i

        ->  Append  (cost=3.40..38.95 rows=680 width=4) (actual time=0.038..0.340 rows=680 loops=1)

              CTE g

                ->  Function Scan on pg_catalog.generate_series  (cost=0.00..3.40 rows=340 width=4) (actual time=0.037..0.062 rows=340 loops=1)

                      Output: generate_series.generate_series

                      Function Call: generate_series(1, 340)

              ->  CTE Scan on g  (cost=0.00..6.80 rows=340 width=4) (actual time=0.037..0.128 rows=340 loops=1)

                    Output: g.i

              ->  Sort  (cost=21.10..21.95 rows=340 width=4) (actual time=0.123..0.146 rows=340 loops=1)

                    Output: g_1.i

                    Sort Key: g_1.i DESC

                    Sort Method: quicksort  Memory: 40kB

                    ->  CTE Scan on g g_1  (cost=0.00..6.80 rows=340 width=4) (actual time=0.001..0.032 rows=340 loops=1)

                          Output: g_1.i

Planning Time: 0.065 ms

Execution Time: 90.512 ms