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 demo (
  id  serial PRIMARY KEY
, a   text
, b   text
);

CREATE TABLE stuff (
  id  serial PRIMARY KEY
, c   text
);

CREATE TABLE demo_stuff (
  demo_id  int REFERENCES demo(id)
, stuff_id int REFERENCES stuff(id)
);

INSERT INTO demo VALUES
  (1,'x','y')
, (2,'x','z')
, (3,'x','z')
;
INSERT INTO stuff VALUES
  (1,'foo')
, (2,'bar')
;
INSERT INTO demo_stuff VALUES
  (1,1)
, (1,2)
;
3 rows affected

2 rows affected

2 rows affected

-- PROBLEM query:
-- this query shows how the join leads to wrong aggregate 
-- results, as it obviously duplicates rows
-- 'y' appears only once in the table, but is counted twice here
SELECT grouping(b), b, count(*) 
FROM   demo d
LEFT   JOIN demo_stuff s ON d.id = s.demo_id 
GROUP  BY GROUPING SETS ((b), ())
ORDER  BY 1;


grouping
b
count


0
z
2

0
y
2

1
null
4


-- SOLUTION:
SELECT grouping(b), b
     , count(DISTINCT d.id) AS ct_distinct_demos
     , count(*)             AS ct_rows
FROM   demo d
LEFT   JOIN demo_stuff s ON d.id = s.demo_id 
GROUP  BY GROUPING SETS ((b), ())
ORDER  BY 1;


grouping
b
ct_distinct_demos
ct_rows


0
y
1
2

0
z
2
2

1
null
3
4