Postgres 13

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

-- minimal demo
CREATE TABLE purchases (
  id       serial
, customer text
, total    int
);

INSERT INTO purchases VALUES
  (1, 'Joe',   5)
, (2, 'Sally', 3)
, (3, 'Joe',   2)
, (4, 'Sally', 1)
;

CREATE TABLE



INSERT 0 4


SELECT DISTINCT ON (customer)
       id, customer, total
FROM   purchases
ORDER  BY customer, total DESC, id;


id
customer
total


1
Joe
5

2
Sally
3



SELECT 2


SELECT DISTINCT ON (2)
       id, customer, total
FROM   purchases
ORDER  BY 2, 3 DESC, 1;


id
customer
total


1
Joe
5

2
Sally
3



SELECT 2


SELECT DISTINCT ON (customer)
       id, customer, total
FROM   purchases
ORDER  BY customer, total DESC NULLS LAST, id;


id
customer
total


1
Joe
5

2
Sally
3



SELECT 2