Postgres 14

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 locations (
location VARCHAR(1),
status TEXT,
price INT
);

insert into locations values ('A', 'sold', 3);

create table history (
 location VARCHAR(1),
 field TEXT,
 oldval_str TEXT,
 newval_str TEXT,
 oldval_num INT,
 newval_num INT,
 created_at DATE
);

insert into history 
    VALUES 
        ('A', 'status', 'closed', 'sold', null, null, '2022-06-01')
        ,('A', 'status', 'listed', 'closed', null, null, '2022-05-01')
        ,('A', 'status', null, 'listed', null, null, '2022-04-01')
        ,('A', 'price', null, null, null, 1, '2022-04-01')
        ,('A', 'price', null, null, 1, 2, '2022-05-01')
        ,('A', 'price', null, null, 2, 3, '2022-06-01');
    
1 rows affected

6 rows affected

SELECT
    l.location, 
    MAX(h.newval_str) FILTER (WHERE h.field = 'status') AS status,
    MAX(h.newval_num) FILTER (WHERE h.field = 'price') AS price,
    h.created_at
FROM locations l
JOIN history h ON h.location = l.location
GROUP BY l.location, h.created_at
ORDER BY h.created_at


location
status
price
created_at


A
listed
1
2022-04-01

A
closed
2
2022-05-01

A
sold
3
2022-06-01


select version();


version


PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit