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

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


create table my_table (
ean int,
price decimal,
dtmod timestamp
);

insert into my_table values
(155105, 2.19, '2022-06-22 03:03:25.43045+02'),
(155105, -0.01, '2022-06-28 02:27:15.478475+02'),
(155105, 1.45, '2022-06-28 15:11:35.558692+02'),
(114695, -0.01, '2022-06-28 02:27:15.448782+02'),
(114695, 5.99, '2022-06-28 15:11:27.689637+02'),
(213786, -0.01, '2022-06-28 02:27:15.468477+02'),
(213786, 2.39, '2022-06-28 15:11:32.284314+02');
7 rows affected

select ean, price, dtmod from my_table where dtmod in (select max(dtmod) from my_table group by ean) order by ean;


ean
price
dtmod


114695
5.99
2022-06-28 15:11:27.689637

155105
1.45
2022-06-28 15:11:35.558692

213786
2.39
2022-06-28 15:11:32.284314


SELECT DISTINCT ON (ean)
    ean, 
    LAST_VALUE(price) OVER (PARTITION BY ean ORDER BY dtmod DESC) AS price, 
    MAX(dtmod) OVER (PARTITION BY ean) AS dtmod
FROM my_table


ean
price
dtmod


114695
5.99
2022-06-28 15:11:27.689637

155105
1.45
2022-06-28 15:11:35.558692

213786
2.39
2022-06-28 15:11:32.284314

ean	price	dtmod
114695	5.99	2022-06-28 15:11:27.689637
155105	1.45	2022-06-28 15:11:35.558692
213786	2.39	2022-06-28 15:11:32.284314