Re: pg16: XX000: could not find pathkey item to sort

On Mon, 18 Mar 2024 at 18:50, Ashutosh Bapat
<ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
> If the problem you speculate is different from this one, I am not able to see it. It might help give an example query or explain more.

I looked at this again and I might have been wrong about there being a
problem. I set a breakpoint in create_gather_merge_path() and
adjusted the startup and total cost to 1 when I saw the pathkeys
containing {a,b}. It turns out this is the non-partitionwise
aggregate path, and of course, the targetlist there does contain the
"b" column, so it's fine in that case that the pathkeys are {a,b}. I
had previously thought that this was for the partition-wise aggregate
plan, in which case the targetlist would contain a, sum(b order by b),
of which there's no single value of "b" that we can legally sort by.

Here's the full plan.

postgres=# explain verbose SELECT a, sum(b order by b) FROM t GROUP BY
a ORDER BY a;
QUERY PLAN
---------------------------------------------------------------------------------------------------
GroupAggregate (cost=1.00..25.60 rows=200 width=12)
Output: t.a, sum(t.b ORDER BY t.b)
Group Key: t.a
-> Gather Merge (cost=1.00..1.00 rows=4520 width=8)
Output: t.a, t.b
Workers Planned: 2
-> Sort (cost=158.36..163.07 rows=1882 width=8)
Output: t.a, t.b
Sort Key: t.a, t.b
-> Parallel Append (cost=0.00..56.00 rows=1882 width=8)
-> Parallel Seq Scan on public.tp1 t_1
(cost=0.00..23.29 rows=1329 width=8)
Output: t_1.a, t_1.b
-> Parallel Seq Scan on public.td t_2
(cost=0.00..23.29 rows=1329 width=8)
Output: t_2.a, t_2.b
(14 rows)

David