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 measure_app_scalemeasurement (
store_code text,
certificate_id text,
audit_ending date);
insert into measure_app_scalemeasurement values
('K010','vwv','10.12.2023'),
('K010','cert1','12.12.2023'),
('K054','vwv','14.12.2023'),
('K054','cert1','20.01.2024')
returning *
CREATE TABLE
| store_code | certificate_id | audit_ending |
|---|---|---|
| K010 | vwv | 2023-12-10 |
| K010 | cert1 | 2023-12-12 |
| K054 | vwv | 2023-12-14 |
| K054 | cert1 | 2024-01-20 |
INSERT 0 4
select distinct on(store_code) store_code, certificate_id, audit_ending
from measure_app_scalemeasurement
order by store_code,audit_ending;
| store_code | certificate_id | audit_ending |
|---|---|---|
| K010 | vwv | 2023-12-10 |
| K054 | vwv | 2023-12-14 |
SELECT 2