public inbox for [email protected]
help / color / mirror / Atom feedFrom: David Rowley <[email protected]>
To: Alexander Lakhin <[email protected]>
To: Robert Haas <[email protected]>
To: Ashutosh Bapat <[email protected]>
Cc: Richard Guo <[email protected]>
Cc: Justin Pryzby <[email protected]>
Cc: [email protected]
Subject: Re: pg16: XX000: could not find pathkey item to sort
Date: Thu, 14 Mar 2024 12:00:24 +1300
Message-ID: <CAApHDvqEdHSMwZt5ngXOBK+rZ=WU5iJdkvf+yD7qQJVu606fNg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<CAMbWs48X+cK2pEaZDVVjd+nOJzb1dN1CO1OKz+6D0FX2OTCGbg@mail.gmail.com>
<CAApHDvpJJigQRW29TppTOPYp+Aui0mtd3MpfRxyKv=N-tB62jQ@mail.gmail.com>
<CAApHDvo7RzcQYw-gnkZr6QCijCqf8vJLkJ4XFk-KawvyAw109Q@mail.gmail.com>
<CAApHDvqoMf_kxQKON+FLQh8DbMKe62R=OdrZoenb6xDYWmQVUQ@mail.gmail.com>
<CAMbWs48VKgJyqfDNtKsYnNsPYWqA=stPKenehVz1NN0HTFV9mg@mail.gmail.com>
<CAApHDvrAHkghCDNFG5+K2adWCDCC9cdZT2KZamVRkqYy31kL9w@mail.gmail.com>
<CAApHDvqira92LLPbW37azL+fDEm7yvWni89YekB9G751W7nEBw@mail.gmail.com>
<[email protected]>
On Thu, 14 Mar 2024 at 06:00, Alexander Lakhin <[email protected]> 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.
David
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 443ab08d75..eaba6ddc03 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -7320,6 +7320,15 @@ gather_grouping_paths(PlannerInfo *root, RelOptInfo *rel)
{
ListCell *lc;
Path *cheapest_partial_path;
+ List *groupby_pathkeys;
+
+
+ /* trim off any pathkeys added for ORDER BY / DISTINCT aggregates */
+ if (list_length(root->group_pathkeys) > root->num_groupby_pathkeys)
+ groupby_pathkeys = list_copy_head(root->group_pathkeys,
+ root->num_groupby_pathkeys);
+ else
+ groupby_pathkeys = root->group_pathkeys;
/* Try Gather for unordered paths and Gather Merge for ordered ones. */
generate_useful_gather_paths(root, rel, true);
@@ -7334,7 +7343,7 @@ gather_grouping_paths(PlannerInfo *root, RelOptInfo *rel)
int presorted_keys;
double total_groups;
- is_sorted = pathkeys_count_contained_in(root->group_pathkeys,
+ is_sorted = pathkeys_count_contained_in(groupby_pathkeys,
path->pathkeys,
&presorted_keys);
@@ -7366,7 +7375,7 @@ gather_grouping_paths(PlannerInfo *root, RelOptInfo *rel)
path = (Path *) create_incremental_sort_path(root,
rel,
path,
- root->group_pathkeys,
+ groupby_pathkeys,
presorted_keys,
-1.0);
@@ -7375,7 +7384,7 @@ gather_grouping_paths(PlannerInfo *root, RelOptInfo *rel)
rel,
path,
rel->reltarget,
- root->group_pathkeys,
+ groupby_pathkeys,
NULL,
&total_groups);
Attachments:
[text/plain] fix_groupby_pathkeys.patch (1.6K, ../CAApHDvqEdHSMwZt5ngXOBK+rZ=WU5iJdkvf+yD7qQJVu606fNg@mail.gmail.com/2-fix_groupby_pathkeys.patch)
download | inline diff:
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 443ab08d75..eaba6ddc03 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -7320,6 +7320,15 @@ gather_grouping_paths(PlannerInfo *root, RelOptInfo *rel)
{
ListCell *lc;
Path *cheapest_partial_path;
+ List *groupby_pathkeys;
+
+
+ /* trim off any pathkeys added for ORDER BY / DISTINCT aggregates */
+ if (list_length(root->group_pathkeys) > root->num_groupby_pathkeys)
+ groupby_pathkeys = list_copy_head(root->group_pathkeys,
+ root->num_groupby_pathkeys);
+ else
+ groupby_pathkeys = root->group_pathkeys;
/* Try Gather for unordered paths and Gather Merge for ordered ones. */
generate_useful_gather_paths(root, rel, true);
@@ -7334,7 +7343,7 @@ gather_grouping_paths(PlannerInfo *root, RelOptInfo *rel)
int presorted_keys;
double total_groups;
- is_sorted = pathkeys_count_contained_in(root->group_pathkeys,
+ is_sorted = pathkeys_count_contained_in(groupby_pathkeys,
path->pathkeys,
&presorted_keys);
@@ -7366,7 +7375,7 @@ gather_grouping_paths(PlannerInfo *root, RelOptInfo *rel)
path = (Path *) create_incremental_sort_path(root,
rel,
path,
- root->group_pathkeys,
+ groupby_pathkeys,
presorted_keys,
-1.0);
@@ -7375,7 +7384,7 @@ gather_grouping_paths(PlannerInfo *root, RelOptInfo *rel)
rel,
path,
rel->reltarget,
- root->group_pathkeys,
+ groupby_pathkeys,
NULL,
&total_groups);
view thread (15+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: pg16: XX000: could not find pathkey item to sort
In-Reply-To: <CAApHDvqEdHSMwZt5ngXOBK+rZ=WU5iJdkvf+yD7qQJVu606fNg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox