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

--
-- McNet's solution
--
create or replace function generate_up_down_series(maxNum int, rep int)
returns table (serie int) as
$body$
declare
    x int;
    y int;
    z int;
BEGIN

    x := 0;
    while x < rep loop

        y := 1;
        while y <= maxNum loop
            serie := y;
            return next;
            y := y + 1;
        end loop;

        z := maxNum;
        while z > 0 loop
            serie := z;
            return next;
            z := z - 1;
        end loop;

        x := x + 1;
    end loop;

END;
$body$ LANGUAGE plpgsql IMMUTABLE STRICT;
SELECT generate_up_down_series(3, 5);

generate_up_down_series

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)  --  ~ 0.075 ms
SELECT generate_up_down_series(3, 5);

QUERY PLAN

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

  Output: generate_up_down_series(3, 5)

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

Planning Time: 0.012 ms

Execution Time: 0.082 ms