public inbox for [email protected]
help / color / mirror / Atom feedFrom: David Rowley <[email protected]>
To: Richard Guo <[email protected]>
Cc: Justin Pryzby <[email protected]>
Cc: [email protected]
Subject: Re: pg16: XX000: could not find pathkey item to sort
Date: Mon, 9 Oct 2023 17:13:09 +1300
Message-ID: <CAApHDvqira92LLPbW37azL+fDEm7yvWni89YekB9G751W7nEBw@mail.gmail.com> (raw)
In-Reply-To: <CAApHDvrAHkghCDNFG5+K2adWCDCC9cdZT2KZamVRkqYy31kL9w@mail.gmail.com>
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>
On Mon, 9 Oct 2023 at 12:42, David Rowley <[email protected]> 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
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 1e4dd27dba..3c713782f1 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -17,6 +17,7 @@
#include <limits.h>
#include <math.h>
+#include <time.h>
#include "access/genam.h"
#include "access/htup_details.h"
@@ -274,11 +275,22 @@ planner(Query *parse, const char *query_string, int cursorOptions,
ParamListInfo boundParams)
{
PlannedStmt *result;
+ struct timespec start, end;
+
+ clock_gettime(CLOCK_PROCESS_CPUTIME_ID, &start);
if (planner_hook)
result = (*planner_hook) (parse, query_string, cursorOptions, boundParams);
else
result = standard_planner(parse, query_string, cursorOptions, boundParams);
+
+ clock_gettime(CLOCK_PROCESS_CPUTIME_ID, &end);
+
+ elog(LOG,
+ "planning time in %f nanoseconds",
+ ((double) (end.tv_sec * 1000000000 + end.tv_nsec) -
+ (double) (start.tv_sec * 1000000000 + start.tv_nsec)));
+
return result;
}
Attachments:
[text/plain] plan_times.patch.txt (1014B, ../CAApHDvqira92LLPbW37azL+fDEm7yvWni89YekB9G751W7nEBw@mail.gmail.com/2-plan_times.patch.txt)
download | inline diff:
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 1e4dd27dba..3c713782f1 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -17,6 +17,7 @@
#include <limits.h>
#include <math.h>
+#include <time.h>
#include "access/genam.h"
#include "access/htup_details.h"
@@ -274,11 +275,22 @@ planner(Query *parse, const char *query_string, int cursorOptions,
ParamListInfo boundParams)
{
PlannedStmt *result;
+ struct timespec start, end;
+
+ clock_gettime(CLOCK_PROCESS_CPUTIME_ID, &start);
if (planner_hook)
result = (*planner_hook) (parse, query_string, cursorOptions, boundParams);
else
result = standard_planner(parse, query_string, cursorOptions, boundParams);
+
+ clock_gettime(CLOCK_PROCESS_CPUTIME_ID, &end);
+
+ elog(LOG,
+ "planning time in %f nanoseconds",
+ ((double) (end.tv_sec * 1000000000 + end.tv_nsec) -
+ (double) (start.tv_sec * 1000000000 + start.tv_nsec)));
+
return result;
}
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]
Subject: Re: pg16: XX000: could not find pathkey item to sort
In-Reply-To: <CAApHDvqira92LLPbW37azL+fDEm7yvWni89YekB9G751W7nEBw@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