Postgres 10

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 error( id INT, name VARCHAR(100) );
CREATE TABLE table_c( value_id INT, process INT );
CREATE TABLE table_b( id INT );
CREATE TABLE table_a( id INT, root_id INT );
INSERT INTO table_a VALUES(1,5);
INSERT INTO table_b VALUES(5);
INSERT INTO table_c VALUES(7,1);
INSERT INTO error VALUES(7,'PASSWORD_PROTECTED');
INSERT INTO error VALUES(7,'PASSWORD_PROTECTED');

INSERT INTO table_a VALUES(2,3);
INSERT INTO table_b VALUES(3);
INSERT INTO table_c VALUES(1,2);
INSERT INTO error VALUES(1,'UNKNOWN');
INSERT INTO error VALUES(1,'UNKNOWN');

INSERT INTO table_a VALUES(3,3);
INSERT INTO table_b VALUES(3);
INSERT INTO table_c VALUES(2,3);
INSERT INTO error VALUES(2,'FILE_NOT_FOUND');
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

        SELECT a.root_id AS id, e.name, COUNT(c.value_id) AS total
          FROM table_c c
          JOIN error e
            ON e.id = c.value_id
          JOIN table_a a 
            ON c.process = a.id  
         GROUP BY e.name, a.root_id
         


id
name
total


3
FILE_NOT_FOUND
1

5
PASSWORD_PROTECTED
2

3
UNKNOWN
2


SELECT q.id, SUM(q.total) AS overall_total,
       JSONB_AGG(JSONB_BUILD_OBJECT('count', q.total, 'type', q.name)) AS totals
  FROM (
        SELECT a.root_id AS id, e.name, COUNT(c.value_id) AS total
          FROM table_c c
          JOIN error e
            ON e.id = c.value_id
          JOIN table_a a 
            ON c.process = a.id  
         GROUP BY e.name, a.root_id  ) q
 GROUP BY q.id


id
overall_total
totals


3
3
[{"type": "FILE_NOT_FOUND", "count": 1}, {"type": "UNKNOWN", "count": 2}]

5
2
[{"type": "PASSWORD_PROTECTED", "count": 2}]

id	overall_total	totals
3	3	[{"type": "FILE_NOT_FOUND", "count": 1}, {"type": "UNKNOWN", "count": 2}]
5	2	[{"type": "PASSWORD_PROTECTED", "count": 2}]