Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test(id int, a INT, b INT, c INT);


CREATE TABLE


INSERT INTO test VALUES (1,11,12,13), (2,21,22,23),(3,31,32,33);

INSERT 0 3


SELECT * FROM test


id
a
b
c


1
11
12
13

2
21
22
23

3
31
32
33



SELECT 3


SELECT t.id, col_name, col_value
FROM test t
JOIN LATERAL (VALUES('a', t.a),('b', t.b),('c',t.c)) s(col_name, col_value) ON TRUE


id
col_name
col_value


1
a
11

1
b
12

1
c
13

2
a
21

2
b
22

2
c
23

3
a
31

3
b
32

3
c
33



SELECT 9


SELECT t.id, col_name, col_value
FROM test t
CROSS JOIN LATERAL (VALUES('a', t.a),('b', t.b),('c',t.c)) s(col_name, col_value)


id
col_name
col_value


1
a
11

1
b
12

1
c
13

2
a
21

2
b
22

2
c
23

3
a
31

3
b
32

3
c
33



SELECT 9


SELECT t.id, col_name, col_value
FROM test t
,LATERAL (VALUES('a', t.a),('b', t.b),('c',t.c)) s(col_name, col_value)


id
col_name
col_value


1
a
11

1
b
12

1
c
13

2
a
21

2
b
22

2
c
23

3
a
31

3
b
32

3
c
33



SELECT 9


-- SINGLE table SCAN

explain (analyze,buffers)SELECT t.id, col_name, col_value
FROM test t
JOIN LATERAL (VALUES('a', t.a),('b', t.b),('c',t.c)) s(col_name, col_value) ON TRUE


QUERY PLAN


Nested Loop  (cost=0.00..153.38 rows=5550 width=40) (actual time=0.031..0.040 rows=9 loops=1)

  Buffers: shared hit=1

  ->  Seq Scan on test t  (cost=0.00..28.50 rows=1850 width=16) (actual time=0.024..0.025 rows=3 loops=1)

        Buffers: shared hit=1

  ->  Values Scan on "*VALUES*"  (cost=0.00..0.04 rows=3 width=36) (actual time=0.001..0.002 rows=3 loops=3)

Planning Time: 0.108 ms

Execution Time: 0.312 ms



EXPLAIN