commit 1349d2790bf48a4de072931c722f39337e72055e Author: David Rowley <drowley@postgresql.org> Date: Tue Aug 2 23:11:45 2022 +1200
Improve performance of ORDER BY / DISTINCT aggregates
ts=# CREATE TABLE t (a int, b text) PARTITION BY RANGE (a); ts=# CREATE TABLE td PARTITION OF t DEFAULT; ts=# INSERT INTO t SELECT 1 AS a, '' AS b; ts=# SET enable_partitionwise_aggregate=on; ts=# explain SELECT a, COUNT(DISTINCT b) FROM t GROUP BY a; ERROR: XX000: could not find pathkey item to sort LOCATION: prepare_sort_from_pathkeys, createplan.c:6235
Thanks for the report! I've looked at it a little bit. In function adjust_group_pathkeys_for_groupagg we add the pathkeys in ordered aggregates to root->group_pathkeys. But if the new added pathkeys do not have EC members that match the targetlist or can be computed from the targetlist, prepare_sort_from_pathkeys would have problem computing sort column info for the new added pathkeys. In the given example, the pathkey representing 'b' can not match or be computed from the current targetlist, so prepare_sort_from_pathkeys emits the error.
My first thought about the fix is that we artificially add resjunk target entries to parse->targetList for the ordered aggregates' arguments that are ORDER BY expressions, as attached. While this can fix the given query, it would cause Assert failure for the query in sql/triggers.sql.
-- inserts only insert into my_table values (1, 'AAA'), (2, 'BBB') on conflict (a) do update set b = my_table.b || ':' || excluded.b;