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

CREATE TABLE product(id int, quantity int);
INSERT INTO product VALUES
  (1,   1)
, (2, 666)
, (3, NULL)
, (4,  33)
, (5, 666)
, (6,  -1)
, (7, 666)
;

CREATE TABLE

INSERT 0 7

CREATE TABLE product1(id int, quantity int);
INSERT INTO product1 VALUES
  (1, NULL)
, (2, NULL)
, (3, NULL)
;

CREATE TABLE

INSERT 0 3

TABLE product;

id
quantity

1
1

2
666

3
null

4
33

5
666

6
-1

7
666

SELECT 7

SELECT id, quantity
FROM   product
ORDER  BY quantity DESC NULLS LAST
FETCH  FIRST 1 ROWS WITH TIES;

id
quantity

5
666

2
666

7
666

SELECT 3

SELECT id, quantity
FROM   product
ORDER  BY quantity DESC NULLS LAST
FETCH  NEXT 1 ROW WITH TIES;

id
quantity

5
666

2
666

7
666

SELECT 3

-- ONLY instead of WITH TIES returns exactly 1 row (if any):
SELECT id, quantity FROM product
ORDER BY quantity DESC NULLS LAST
FETCH NEXT 1 ROW ONLY;

id
quantity

2
666

SELECT 1

SELECT id, quantity
FROM   product
WHERE  quantity IS NOT DISTINCT FROM (SELECT MAX(quantity) FROM product);

id
quantity

2
666

5
666

7
666

SELECT 3

SELECT id, quantity
FROM  (
   SELECT *, rank() OVER (ORDER BY quantity DESC NULLS LAST) AS rnk
   FROM   product
   ) sub
WHERE  rnk = 1;

id
quantity

5
666

2
666

7
666

SELECT 3

-- even works with all NULL values:
SELECT id, quantity
FROM   product1
WHERE  quantity IS NOT DISTINCT FROM (SELECT MAX(quantity) FROM product1);

id
quantity

1
null

2
null

3
null

SELECT 3

SELECT id, quantity
FROM  (
   SELECT *, rank() OVER (ORDER BY quantity DESC NULLS LAST) AS rnk
   FROM   product1
   ) sub
WHERE  rnk = 1;

id
quantity

1
null

2
null

3
null

SELECT 3

-- your original doesn't
SELECT id, quantity FROM product1 WHERE quantity = (SELECT MAX(quantity) FROM product1);

id
quantity

SELECT 0

id	quantity
1	null
2	null
3	null

id	quantity
1	null
2	null
3	null