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 tablename (
fruit VARCHAR(6),
ripeness INTEGER
);
INSERT INTO tablename
(fruit, ripeness)
VALUES
('orange', '1'),
('orange', '3'),
('apple', '0'),
('apple', '3'),
('apple', '3'),
('apple', '2');
CREATE TABLE
INSERT 0 6
SELECT f.fruit, r.ripeness,
COUNT(t.fruit) counter
FROM (SELECT DISTINCT fruit FROM tablename) f
CROSS JOIN (SELECT DISTINCT ripeness FROM tablename) r
LEFT JOIN tablename t ON t.fruit = f.fruit AND t.ripeness = r.ripeness
GROUP BY ROLLUP(f.fruit, r.ripeness)
ORDER BY f.fruit, r.ripeness;
| fruit | ripeness | counter |
|---|---|---|
| apple | 0 | 1 |
| apple | 1 | 0 |
| apple | 2 | 1 |
| apple | 3 | 2 |
| apple | null | 4 |
| orange | 0 | 0 |
| orange | 1 | 1 |
| orange | 2 | 0 |
| orange | 3 | 1 |
| orange | null | 2 |
| null | null | 6 |
SELECT 11