Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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 param(n INT, m INT);
INSERT INTO param VALUES (3, 5);
SELECT * FROM param;
1 rows affected

n
m

3
5

CREATE TABLE numbers 
( n int not null primary key ) ;
INSERT INTO numbers
SELECT x FROM GENERATE_SERIES(1, (SELECT m FROM param)) AS t(x);
5 rows affected

SELECT 
    CASE WHEN g2.i = 1 THEN gn.i ELSE p.n + 1 - gn.i END AS xi
FROM
    param 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

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT 
    CASE WHEN g2.i = 1 THEN gn.i ELSE p.n + 1 - gn.i END AS xi
FROM
    param 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=473756050859.16..477225857109.16 rows=1387922500000 width=16) (actual time=0.111..0.114 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=11

  ->  Nested Loop  (cost=11.05..27803745221.89 rows=1387922500000 width=16) (actual time=0.064..0.090 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=11

        ->  Nested Loop  (cost=0.31..45295188.40 rows=1632850000 width=12) (actual time=0.044..0.058 rows=15 loops=1)

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

              Buffers: shared hit=9

              ->  Nested Loop  (cost=0.15..53258.40 rows=1921000 width=12) (actual time=0.031..0.034 rows=3 loops=1)

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

                    Buffers: shared hit=3

                    ->  Seq Scan on public.param p  (cost=0.00..32.60 rows=2260 width=8) (actual time=0.009..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.15..15.05 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=2

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

                    Output: gm.i

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

                    Heap Fetches: 15

                    Buffers: shared hit=6

        ->  Materialize  (cost=10.74..35.62 rows=850 width=4) (actual time=0.001..0.002 rows=2 loops=15)

              Output: g2.i

              Buffers: shared hit=2

              ->  Bitmap Heap Scan on public.numbers g2  (cost=10.74..31.37 rows=850 width=4) (actual time=0.005..0.006 rows=2 loops=1)

                    Output: g2.i

                    Recheck Cond: (g2.i <= 2)

                    Heap Blocks: exact=1

                    Buffers: shared hit=2

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

                          Index Cond: (g2.i <= 2)

                          Buffers: shared hit=1

Planning Time: 0.143 ms

Execution Time: 0.178 ms