On Tue, Oct 29, 2024 at 3:51 AM Richard Guo <guofengli...@gmail.com> wrote: > > 2. join type is FULL JOIN, (i am not sure about other Semijoins and > > anti-semijoins types). > > The presence of a FULL JOIN does not preclude the use of eager > aggregation. We still can push a partial aggregation down to a level > that is above the FULL JOIN.
I think you can also push a partial aggregation step through a FULL JOIN. Consider this: SELECT p.name, string_agg(c.name, ', ') FROM parents p FULL JOIN children c ON p.id = c.parent_id GROUP BY 1; I don't see why it matters here that this is a FULL JOIN. If it were an inner join, we'd have one group for every parent that has at least one child. Since it's a full join, we'll also get one group for every parent with no children, and also one group for the null parent. But that should work fine with a partially aggregated c. -- Robert Haas EDB: http://www.enterprisedb.com
