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

From Ashutosh Bapat
Subject Re: pg16: XX000: could not find pathkey item to sort
Date
Msg-id CAExHW5vi8Fup_cQeR_KswNWxusBBEOy79wFRPzt2MK0oemQ5tg@mail.gmail.com
Whole thread Raw
In response to Re: pg16: XX000: could not find pathkey item to sort  (David Rowley <dgrowleyml@gmail.com>)
Responses Re: pg16: XX000: could not find pathkey item to sort
List pgsql-hackers


On Thu, Mar 14, 2024 at 4:30 AM David Rowley <dgrowleyml@gmail.com> wrote:
On Thu, 14 Mar 2024 at 06:00, Alexander Lakhin <exclusion@gmail.com> wrote:
> I've stumbled upon the same error, but this time it apparently has another
> cause. It can be produced (on REL_16_STABLE and master) as follows:
> CREATE TABLE t (a int, b int) PARTITION BY RANGE (a);
> CREATE TABLE td PARTITION OF t DEFAULT;
> CREATE TABLE tp1 PARTITION OF t FOR VALUES FROM (1) TO (2);
> SET enable_partitionwise_aggregate = on;
> SET parallel_setup_cost = 0;
> SELECT a, sum(b order by b) FROM t GROUP BY a ORDER BY a;
>
> ERROR:  could not find pathkey item to sort
>
> `git bisect` for this anomaly blames the same commit 1349d2790.

Thanks for finding and for the recreator script.

I've attached a patch which fixes the problem for me.

On debugging this I uncovered some other stuff that looks broken which
seems to caused by partition-wise aggregates.  With your example
query, in get_useful_pathkeys_for_relation(), we call
relation_can_be_sorted_early() to check if the pathkey can be used as
a set of pathkeys in useful_pathkeys_list.  The problem is that in
your query the 'rel' is the base relation belonging to the partitioned
table and relation_can_be_sorted_early() looks through the targetlist
for that relation and finds columns "a" and "b" in there. The problem
is "b" has been aggregated away as partial aggregation has taken place
due to the partition-wise aggregation. I believe whichever rel we
should be using there should have an Aggref in the target exprs rather
than the plain unaggregated column.  I've added Robert and Ashutosh to
see what their thoughts are on this.

I don't understand why root->query_pathkeys has both a and b. "a" is there because of GROUP BY and ORDER BY clause. But why "b"?

Under the debugger this is what I observed: generate_useful_gather_paths() gets called twice, once for the base relation and second time for the upper relation.

When it's called for base relation, it includes "a" and "b" both in the useful pathkeys. The plan doesn't use sortedness on b. But I don't think that's the problem of the relation used. It looks like root->query_pathkeys containing "b" may be a problem.

When it's called for upper relation, the reltarget has "a" and Aggref() and it includes only "a" in the useful pathkeys which is as per your expectation.

--
Best Wishes,
Ashutosh Bapat

pgsql-hackers by date:

Previous
From: "Tristan Partin"
Date:
Subject: Re: Remove a FIXME and unused variables in Meson
Next
From: Amit Kapila
Date:
Subject: Re: Have pg_basebackup write "dbname" in "primary_conninfo"?