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 sales
(
cust varchar(20),
prod varchar(20),
day integer,
month integer,
year integer,
state char(2),
quant integer
);
insert into sales values ('Bloom', 'Pepsi', 2, 12, 2001, 'NY', 4232);
insert into sales values ('Knuth', 'Bread', 23, 5, 2005, 'PA', 4167);
insert into sales values ('Emily', 'Pepsi', 22, 1, 2006, 'CT', 4404);
insert into sales values ('Emily', 'Fruits', 11, 1, 2000, 'NJ', 4369);
insert into sales values ('Helen', 'Milk', 7, 11, 2006, 'CT', 210);
WITH custprod AS (
SELECT DISTINCT cust, prod
FROM sales
),
ny_sales AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY cust, prod ORDER BY quant DESC) rn
FROM sales
WHERE state = 'NY'
),
nj_sales AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY cust, prod ORDER BY quant) rn
FROM sales
WHERE state = 'NJ'
),
ct_sales AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY cust, prod ORDER BY quant) rn
FROM sales
WHERE state = 'CT'
)
| cust | prod | ny_max | ny_date | nj_max | nj_date | ct_max | ct_date |
|---|---|---|---|---|---|---|---|
| Bloom | Pepsi | 4232 | 2001-12-2 | null | null | null | null |
| Emily | Fruits | null | null | 4369 | 2000-1-11 | null | null |
| Emily | Pepsi | null | null | null | null | 4404 | 2006-1-22 |
| Helen | Milk | null | null | null | null | 210 | 2006-11-7 |
| Knuth | Bread | null | null | null | null | null | null |
SELECT 5