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 products (
  id INT PRIMARY KEY,
  sku TEXT NOT NULL,
  fee REAL
);
CREATE TABLE fee_change(
  id SERIAL PRIMARY KEY,
  sku_id INT NOT NULL,
  old_fee REAL NOT NULL,
  new_fee REAL NOT NULL,
  FOREIGN KEY (sku_id) REFERENCES products(id)
);
INSERT INTO products(id,sku,fee) VALUES (1,'ASC',2);
INSERT INTO products(id,sku,fee) VALUES (2,'CF2',3.6);
INSERT INTO products(id,sku,fee) VALUES (3,'RTG',1.2);
INSERT INTO products(id,sku,fee) VALUES (4,'VHN5',3);

SELECT * FROM products;
1 rows affected

1 rows affected

1 rows affected

1 rows affected



id
sku
fee


1
ASC
2

2
CF2
3.6

3
RTG
1.2

4
VHN5
3


INSERT INTO fee_change(sku_id,old_fee,new_fee) VALUES(1,2,3);
INSERT INTO fee_change(sku_id,old_fee,new_fee) VALUES(1,3,4);
INSERT INTO fee_change(sku_id,old_fee,new_fee) VALUES(1,4,2.5);
INSERT INTO fee_change(sku_id,old_fee,new_fee) VALUES(2,3,4);
INSERT INTO fee_change(sku_id,old_fee,new_fee) VALUES(2,4,1);
INSERT INTO fee_change(sku_id,old_fee,new_fee) VALUES(3,1,2.4);
INSERT INTO fee_change(sku_id,old_fee,new_fee) VALUES(3,2.4,3.1);
INSERT INTO fee_change(sku_id,old_fee,new_fee) VALUES(3,3.1,2);
INSERT INTO fee_change(sku_id,old_fee,new_fee) VALUES(3,2,0.5);
INSERT INTO fee_change(sku_id,old_fee,new_fee) VALUES(3,0.5,1);

SELECT * FROM fee_change;
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected



id
sku_id
old_fee
new_fee


1
1
2
3

2
1
3
4

3
1
4
2.5

4
2
3
4

5
2
4
1

6
3
1
2.4

7
3
2.4
3.1

8
3
3.1
2

9
3
2
0.5

10
3
0.5
1


SELECT 
    p.id, p.sku, p.fee,
    MAX(old_fee) FILTER (WHERE row_number = 1) AS old_fee_1,
    MAX(new_fee) FILTER (WHERE row_number = 1) AS new_fee_1,
    MAX(old_fee) FILTER (WHERE row_number = 2) AS old_fee_2,
    MAX(new_fee) FILTER (WHERE row_number = 2) AS new_fee_2
    
FROM products AS p
LEFT JOIN LATERAL (
    SELECT 
        *,
        row_number() OVER (PARTITION BY sku_id)
    FROM fee_change
    WHERE sku_id = p.id
    ORDER BY id DESC
    LIMIT 2
) AS oo ON true

GROUP BY p.id, p.sku, p.fee


id
sku
fee
old_fee_1
new_fee_1
old_fee_2
new_fee_2


1
ASC
2
4
2.5
3
4

2
CF2
3.6
4
1
3
4

3
RTG
1.2
0.5
1
2
0.5

4
VHN5
3
null
null
null
null

id	sku	fee	old_fee_1	new_fee_1	old_fee_2	new_fee_2
1	ASC	2	4	2.5	3	4
2	CF2	3.6	4	1	3	4
3	RTG	1.2	0.5	1	2	0.5
4	VHN5	3	null	null	null	null