Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rkdZG-00ECc2-5x for pgsql-hackers@arkaria.postgresql.org; Thu, 14 Mar 2024 05:23:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rkdZD-00FDy0-Nk for pgsql-hackers@arkaria.postgresql.org; Thu, 14 Mar 2024 05:23:48 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rkdZD-00FDxH-7h for pgsql-hackers@lists.postgresql.org; Thu, 14 Mar 2024 05:23:47 +0000 Received: from mail-vs1-xe2b.google.com ([2607:f8b0:4864:20::e2b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rkdZ9-004P0Y-JW for pgsql-hackers@lists.postgresql.org; Thu, 14 Mar 2024 05:23:46 +0000 Received: by mail-vs1-xe2b.google.com with SMTP id ada2fe7eead31-475ffc62cbaso222984137.1 for ; Wed, 13 Mar 2024 22:23:44 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1710393824; x=1710998624; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=IGQDT4YloDB1sftsIOozihWO9p9l3X3Fqmjvf1ueApA=; b=j4KCHpjRDTLbRLYlhq0rUW0InLeGTrNFe0MX2nsP3mSTyCGH1pavyeHwHdPRvVZxZt scvu4R5RkbDYmDGqDyJvHCmsdD6WHW7NgeopzRvP6GwjGTJGmAaZVeNJQdUVBMMDl8Nq bjJXLObfFBZ8XxLbtftFgWZ/uybpRrOR6OJ2I94sOsQHoBRpmihDf1JIFXPJoGnjdMhi TBgvhOtPZsau+aQI8G8Uimo6LN7gtbB8OxY4WKGPsak71usRzuHZaFzvC1PoYBGU9Nba zMJ1xU92qD8KxAl89ESHABv1FZlV8hHbKQSF0IOEjPEYUVF+jlW6zkJGlu4oMmQWX6WN FzIg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1710393824; x=1710998624; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=IGQDT4YloDB1sftsIOozihWO9p9l3X3Fqmjvf1ueApA=; b=GyFMRE+uHndl3gsS5y+ghlCCxUrZdw6HOd0z48tPmrvyV+PPZ7T7iyR9/FB7nGCj1Y a7O0LvLHpAF4oOgd5uNl3oYqIvbbfjdErH00HyhKG2+Sfpivso1w7sZLLBIinZ1zdW/S Fk46coyZrgqI3e41H8c0qRV4L8wO+JMs+5iMpwRZpDoJLE1qgSBe+5o2mpWCcc0uqrCc OkRMRCeOISYYv+BM5wCO+9kJfWvl3z3dpC0R35BapfbHaBsnqDqiqkkbP8h6jrs6exxh uD9Y31QR8Cfo7W+rZAi20Vl6JSks8LjD5TzWg0rJIW9Dy3QBp6XWOhk5yxQGMl3fP0+C ofCw== X-Forwarded-Encrypted: i=1; AJvYcCXdA1zEtSSvEDZyKM6INKHqeExe5j8FKnGpxlPsBweZquPwWxyxT+PZ/hPV1jYDHcm0KJLrHqQNzQ5/nGpcERNJdIar0bNNVGdEZCXMXYrdI10Y X-Gm-Message-State: AOJu0Yy6DWRuZtbm/kP2G0HFQ8iLE9thFNn07oF9cjsrK7xrVLDgzfj5 JdxoqglGyJOLlq+Za+jt1lX3E7t0mOeDMy1InQUV4NeJhu01mU6FU6f8DfuTXdWGHGeKjcFslCb FGWgv/hCnNw8clK6YJv2EVieCDqc= X-Google-Smtp-Source: AGHT+IFShMoZZlP3jQiwvm/4SUJu3YyH74B6Opyz2sSU33dJK0iZN3ESrlOvoL39srFkhJOHZgHlUO4fQii0ORCSHa4= X-Received: by 2002:a67:f744:0:b0:472:c70e:ba55 with SMTP id w4-20020a67f744000000b00472c70eba55mr774536vso.34.1710393823877; Wed, 13 Mar 2024 22:23:43 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ashutosh Bapat Date: Thu, 14 Mar 2024 10:53:32 +0530 Message-ID: Subject: Re: pg16: XX000: could not find pathkey item to sort To: David Rowley Cc: Alexander Lakhin , Robert Haas , Richard Guo , Justin Pryzby , pgsql-hackers@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000004c609e06139818a2" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000004c609e06139818a2 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, Mar 14, 2024 at 4:30=E2=80=AFAM David Rowley = wrote: > On Thu, 14 Mar 2024 at 06:00, Alexander Lakhin > 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 =3D on; > > SET parallel_setup_cost =3D 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. --=20 Best Wishes, Ashutosh Bapat --0000000000004c609e06139818a2 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
On Thu, Mar 14, 2024 at 4:30=E2=80=AF= AM David Rowley <dgrowleyml@gmai= l.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 =3D on;
> SET parallel_setup_cost =3D 0;
> SELECT a, sum(b order by b) FROM t GROUP BY a ORDER BY a;
>
> ERROR:=C2=A0 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.=C2=A0 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.=C2=A0 The problem is that in
your query the 'rel' is the base relation belonging to the partitio= ned
table and relation_can_be_sorted_early() looks through the targetlist
for that relation and finds columns "a" and "b" in ther= e. 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.=C2=A0 I've added Robert and Ashutos= h to
see what their thoughts are on this.
I don't understand why root->query_pathkeys has bot= h 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 r= elation 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 include= s only "a" in the useful pathkeys which is as per your expectatio= n.

-- =
Best Wishes,
Ashutosh Bapat
--0000000000004c609e06139818a2--