Re: pg16: XX000: could not find pathkey item to sort - Mailing list pgsql-hackers

Thanks. To me, it looks like we are gathering pathkeys, which if used to sort the result of overall join, would avoid sorting in as many as aggregates as possible.

relation_can_be_sorted_early() finds, pathkeys which if used to sort the given relation, would help sorting the overall join. Contrary to what I said earlier, it might help if the base relation is sorted on "a" and "b". What I find weird is that the sorting is not pushed down to the partitions, where it would help most.

#explain verbose SELECT a, sum(b order by b) FROM t GROUP BY a ORDER BY a;
                                     QUERY PLAN                                    
------------------------------------------------------------------------------------
 GroupAggregate  (cost=362.21..398.11 rows=200 width=12)
   Output: t.a, sum(t.b ORDER BY t.b)
   Group Key: t.a
   ->  Sort  (cost=362.21..373.51 rows=4520 width=8)
         Output: t.a, t.b
         Sort Key: t.a, t.b
         ->  Append  (cost=0.00..87.80 rows=4520 width=8)
               ->  Seq Scan on public.tp1 t_1  (cost=0.00..32.60 rows=2260 width=8)
                     Output: t_1.a, t_1.b
               ->  Seq Scan on public.td t_2  (cost=0.00..32.60 rows=2260 width=8)
                     Output: t_2.a, t_2.b
(11 rows)