Postgres 11

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?.

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

-- 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.039 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.013..0.014 rows=3 loops=1)

        Buffers: shared hit=1

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

Planning Time: 0.081 ms

Execution Time: 0.388 ms

EXPLAIN