MIME-Version: 1.0
References: <ZQhYYRhUxpW3PSf9@telsasoft.com> <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>
In-Reply-To: <CAApHDvrAHkghCDNFG5+K2adWCDCC9cdZT2KZamVRkqYy31kL9w@mail.gmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Mon, 9 Oct 2023 17:13:09 +1300
Message-ID: <CAApHDvqira92LLPbW37azL+fDEm7yvWni89YekB9G751W7nEBw@mail.gmail.com>
Subject: Re: pg16: XX000: could not find pathkey item to sort
To: Richard Guo <guofenglinux@gmail.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>, pgsql-hackers@lists.postgresql.org
Content-Type: multipart/mixed; boundary="00000000000080e2c6060740cfa2"
Archived-At: <https://www.postgresql.org/message-id/CAApHDvqira92LLPbW37azL%2BfDEm7yvWni89YekB9G751W7nEBw%40mail.gmail.com>
Precedence: bulk

--00000000000080e2c6060740cfa2
Content-Type: text/plain; charset="UTF-8"

On Mon, 9 Oct 2023 at 12:42, David Rowley <dgrowleyml@gmail.com> wrote:
> Maybe it's worth checking the total planning time spent in a run of
> the regression tests with and without the patch to see how much
> overhead it adds to the "average case".

I've now pushed the patch that trims off the Pathkeys for the ORDER BY
/ DISTINCT aggregates.

As for the patch to verify the pathkeys during create plan, I patched
master with the attached plan_times.patch.txt and used the following
to check the time spent in the planner for 3 runs of make
installcheck.

$ for i in {1..3}; do pg_ctl start -D pgdata -l plantime.log >
/dev/null && cd pg_src && make installcheck > /dev/null && cd .. &&
grep "planning time in" plantime.log|sed -E -e 's/.*planning time in
(.*) nanoseconds/\1/'|awk '{nanoseconds += $1} END{print nanoseconds}'
&& pg_ctl stop -D pgdata > /dev/null && rm plantime.log; done

Master:
1855788104
1839655412
1740769066

Patched:
1917797221
1766606115
1881322655

Those results are a bit noisy.  Perhaps a few more runs might yield
more consistency, but it seems that there's not too much overhead to
it. If I take the minimum value out of the 3 runs from each, it comes
to about 1.5% extra time spent in planning.  Perhaps that's OK.

David

--00000000000080e2c6060740cfa2
Content-Type: text/plain; charset="US-ASCII"; name="plan_times.patch.txt"
Content-Disposition: attachment; filename="plan_times.patch.txt"
Content-Transfer-Encoding: base64
Content-ID: <f_lnidisxf0>
X-Attachment-Id: f_lnidisxf0

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL29wdGltaXplci9wbGFuL3BsYW5uZXIuYyBiL3NyYy9i
YWNrZW5kL29wdGltaXplci9wbGFuL3BsYW5uZXIuYwppbmRleCAxZTRkZDI3ZGJhLi4zYzcxMzc4
MmYxIDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9vcHRpbWl6ZXIvcGxhbi9wbGFubmVyLmMKKysr
IGIvc3JjL2JhY2tlbmQvb3B0aW1pemVyL3BsYW4vcGxhbm5lci5jCkBAIC0xNyw2ICsxNyw3IEBA
CiAKICNpbmNsdWRlIDxsaW1pdHMuaD4KICNpbmNsdWRlIDxtYXRoLmg+CisjaW5jbHVkZSA8dGlt
ZS5oPgogCiAjaW5jbHVkZSAiYWNjZXNzL2dlbmFtLmgiCiAjaW5jbHVkZSAiYWNjZXNzL2h0dXBf
ZGV0YWlscy5oIgpAQCAtMjc0LDExICsyNzUsMjIgQEAgcGxhbm5lcihRdWVyeSAqcGFyc2UsIGNv
bnN0IGNoYXIgKnF1ZXJ5X3N0cmluZywgaW50IGN1cnNvck9wdGlvbnMsCiAJCVBhcmFtTGlzdElu
Zm8gYm91bmRQYXJhbXMpCiB7CiAJUGxhbm5lZFN0bXQgKnJlc3VsdDsKKwlzdHJ1Y3QgdGltZXNw
ZWMgc3RhcnQsIGVuZDsKKworCWNsb2NrX2dldHRpbWUoQ0xPQ0tfUFJPQ0VTU19DUFVUSU1FX0lE
LCAmc3RhcnQpOwogCiAJaWYgKHBsYW5uZXJfaG9vaykKIAkJcmVzdWx0ID0gKCpwbGFubmVyX2hv
b2spIChwYXJzZSwgcXVlcnlfc3RyaW5nLCBjdXJzb3JPcHRpb25zLCBib3VuZFBhcmFtcyk7CiAJ
ZWxzZQogCQlyZXN1bHQgPSBzdGFuZGFyZF9wbGFubmVyKHBhcnNlLCBxdWVyeV9zdHJpbmcsIGN1
cnNvck9wdGlvbnMsIGJvdW5kUGFyYW1zKTsKKworCWNsb2NrX2dldHRpbWUoQ0xPQ0tfUFJPQ0VT
U19DUFVUSU1FX0lELCAmZW5kKTsKKworCWVsb2coTE9HLAorCQkgInBsYW5uaW5nIHRpbWUgaW4g
JWYgbmFub3NlY29uZHMiLAorCQkgKChkb3VibGUpIChlbmQudHZfc2VjICogMTAwMDAwMDAwMCAr
IGVuZC50dl9uc2VjKSAtCisJCQkJIChkb3VibGUpIChzdGFydC50dl9zZWMgKiAxMDAwMDAwMDAw
ICsgc3RhcnQudHZfbnNlYykpKTsKKwogCXJldHVybiByZXN1bHQ7CiB9CiAK
--00000000000080e2c6060740cfa2--