Postgres 16

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 invoice (
  id  serial,
  amount DOUBLE precision DEFAULT NULL,
  name VARCHAR(100) DEFAULT NULL
);

CREATE TABLE

INSERT INTO invoice (amount, name) 
VALUES (4.0, 'Michael'), (15.0, 'Bara'), (4.0, 'Michael'), (6.0, 'John');

INSERT 0 4

SELECT * FROM invoice;

id
amount
name

1
4
Michael

2
15
Bara

3
4
Michael

4
6
John

SELECT 4

SELECT sum(amount), name
from invoice
group by name
having sum(amount) > 6

sum
name

8
Michael

15
Bara

SELECT 2

SELECT sum(amount), name
from invoice
group by name
having (select name = 'John') -- refer parent data in subquery - ok!

sum
name

6
John

SELECT 1

SELECT sum(amount), name
from invoice
group by name
having (select sum(amount) > 6) -- refer parent aggregate data in subquery - ok!

sum
name

8
Michael

15
Bara

SELECT 2

SELECT sum(amount), name
from invoice
group by name
having (select sum(amount) > 6 from (select 1) t) -- still ok!

sum
name

8
Michael

15
Bara

SELECT 2

SELECT sum(amount), name
from invoice
group by name
having (select v > 6 from (select sum(amount) v) t) -- the same, only the `sum()` expr
                                                    -- is moved inside the subsubquery

sum
name

8
Michael

15
Bara

SELECT 2