On Mon, Sep 18, 2023 at 10:02 PM Justin Pryzby <pry...@telsasoft.com> wrote:
> This fails since 1349d2790b > > commit 1349d2790bf48a4de072931c722f39337e72055e > Author: David Rowley <drow...@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; I haven't looked into how that happens. Any thoughts? Thanks Richard
v1-0001-Include-ordered-aggregates-arguments-in-targetList.patch
Description: Binary data
