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 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit |
SELECT 1
CREATE TABLE mytable (
id INT ,
period_type VARCHAR(20) ,
period INT ,
commission INT );
INSERT INTO mytable VALUES
('123456','Daily','10','10'),
('123456','Monthly','3','11'),
('123456','Daily','5','12'),
('123456','Monthly','1','13'),
('123457','Monthly','6','14'),
('123457','Daily','5','15'),
('123457','Monthly','9','16');
CREATE TABLE
INSERT 0 7
SELECT *
FROM mytable;
| id | period_type | period | commission |
|---|---|---|---|
| 123456 | Daily | 10 | 10 |
| 123456 | Monthly | 3 | 11 |
| 123456 | Daily | 5 | 12 |
| 123456 | Monthly | 1 | 13 |
| 123457 | Monthly | 6 | 14 |
| 123457 | Daily | 5 | 15 |
| 123457 | Monthly | 9 | 16 |
SELECT 7
SELECT
m3.id,
m3.period_type,
m3.period,
m4.commission
FROM (
SELECT
m.id,
m.period_type,
max(m.period) as period
FROM mytable m
INNER JOIN (
SELECT DISTINCT
id,
max(period_type) over (partition by id ) period_type
FROM mytable
) x on x.id=m.id and x.period_type=m.period_type
GROUP BY m.id, m.period_type
) m3
INNER JOIN mytable m4 on m4.id=m3.id
and m4.period_type=m3.period_type
and m4.period=m3.period
| id | period_type | period | commission |
|---|---|---|---|
| 123456 | Monthly | 3 | 11 |
| 123457 | Monthly | 9 | 16 |
SELECT 2
SELECT json_agg(m5)
from (
SELECT
m3.id,
m3.period_type,
m3.period,
m4.commission
FROM (
SELECT
m.id,
m.period_type,
max(m.period) as period
FROM mytable m
INNER JOIN (
SELECT DISTINCT
id,
max(period_type) over (partition by id ) period_type
FROM mytable
) x on x.id=m.id and x.period_type=m.period_type
GROUP BY m.id, m.period_type
) m3
INNER JOIN mytable m4 on m4.id=m3.id
and m4.period_type=m3.period_type
and m4.period=m3.period
) m5
| json_agg |
|---|
| [{"id":123456,"period_type":"Monthly","period":3,"commission":11}, {"id":123457,"period_type":"Monthly","period":9,"commission":16}] |
SELECT 1