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?.

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