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

On Thu, Mar 14, 2024 at 4:30 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Thu, 14 Mar 2024 at 06:00, Alexander Lakhin <exclusion(at)gmail(dot)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