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;
ERROR: column "version" does not exist LINE 1: select @@version; ^
CREATE TABLE _order
(
order_id INT NOT NULL PRIMARY KEY,
total_price INT NOT NULL
);
CREATE TABLE
INSERT INTO _order VALUES
(1, 1000), (2, 2000), (3, 3000), (4, 4000);
INSERT 0 4
CREATE TABLE payment
(
order_id INT NOT NULL,
amount INT NOT NULL,
CONSTRAINT payment_order_id_fk FOREIGN KEY (order_id) REFERENCES _order (order_id)
);
CREATE TABLE
CREATE INDEX pt_order_id_ix ON payment (order_id);
CREATE INDEX
INSERT INTO payment VALUES
(1, 500), (2, 2000), (3, 1000), (3, 500), (3, 750); -- note - no payment for order_id = 4
INSERT 0 5
SELECT
o.order_id,
o.total_price - COALESCE(sub.paid, 0)
FROM _order o
LEFT JOIN LATERAL (
SELECT SUM(p.amount) AS paid
FROM payment p
WHERE p.order_id = o.order_id
) AS sub
WHERE o.total_price > ISNULL(sub.paid, 0);
ERROR: syntax error at or near "WHERE" LINE 10: WHERE o.total_price > ISNULL(sub.paid, 0); ^