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);
1 rows affected

1 rows affected

1 rows affected

1 rows affected

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);
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

SELECT *
FROM products AS p
LEFT JOIN LATERAL (
SELECT *
FROM fee_change
WHERE sku_id = p.id
ORDER BY id DESC
LIMIT 2
) AS oo
ON true


id
sku
fee
id
sku_id
old_fee
new_fee


1
ASC
2
3
1
4
2.5

1
ASC
2
2
1
3
4

2
CF2
3.6
5
2
4
1

2
CF2
3.6
4
2
3
4

3
RTG
1.2
10
3
0.5
1

3
RTG
1.2
9
3
2
0.5

4
VHN5
3
null
null
null
null

id	sku	fee	id	sku_id	old_fee	new_fee
1	ASC	2	3	1	4	2.5
1	ASC	2	2	1	3	4
2	CF2	3.6	5	2	4	1
2	CF2	3.6	4	2	3	4
3	RTG	1.2	10	3	0.5	1
3	RTG	1.2	9	3	2	0.5
4	VHN5	3	null	null	null	null