Postgres 15

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 A (
    id serial PRIMARY KEY,
    col_a int,
    col_b int,
    col_c int
);

CREATE TABLE B (
    id serial PRIMARY KEY,
    col_a int,
    col_b int,
    col_e int
);
INSERT INTO A VALUES (1, 13, 2, 3), (2, 7, 3, 4), (3, 42, 4, 5);
INSERT INTO B VALUES (7, 13, 8, 9), (8, 7, 9, 10);

CREATE TABLE



CREATE TABLE



INSERT 0 3



INSERT 0 2


CREATE VIEW my_view AS SELECT a, b from A a join B b USING (col_a);

CREATE VIEW


SELECT * FROM my_view;


a
b


(2,7,3,4)
(8,7,9,10)

(1,13,2,3)
(7,13,8,9)



SELECT 2


SELECT (v.a).*, (v.b).* FROM my_view v;


id
col_a
col_b
col_c
id
col_a
col_b
col_e


2
7
3
4
8
7
9
10

1
13
2
3
7
13
8
9



SELECT 2


SELECT (v.a).col_a, (v.a).id AS a_id, (v.b).id AS b_id FROM my_view v;


col_a
a_id
b_id


7
2
8

13
1
7



SELECT 2