Postgres 14

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 YourTable
    ("group_id" int, "parent" varchar(1), "child" varchar(4))
;
    
INSERT INTO YourTable
    ("group_id", "parent", "child")
VALUES
    (1, 'A', 'E'),
    (1, 'B', NULL),
    (2, 'C', 'F'),
    (2, 'D', NULL),
    (2, 'E', 'G'),
    (3, 'X', NULL),
    (3, 'Y', NULL)
;

7 rows affected

-- unique parents
SELECT group_id, parent
FROM   YourTable
GROUP BY group_id, parent


group_id
parent


3
X

2
E

2
D

1
A

1
B

2
C

3
Y


-- unique children
SELECT group_id, child
FROM   YourTable
WHERE  child IS NOT NULL
GROUP BY group_id, child


group_id
child


1
E

2
F

2
G


-- expected results
SELECT  p.group_id, p.parent, c.child
FROM   ( 
           SELECT group_id, parent
           FROM   YourTable
           GROUP BY group_id, parent
       ) 
       p CROSS JOIN 
       (
           SELECT group_id, child
           FROM   YourTable
           WHERE  child IS NOT NULL
           GROUP BY group_id, child       
       )
       c 
WHERE  p.group_id = c.group_id
ORDER BY p.group_id, c.child, p.parent


group_id
parent
child


1
A
E

1
B
E

2
C
F

2
D
F

2
E
F

2
C
G

2
D
G

2
E
G