On Saturday, November 19, 2011 04:52:10 PM Tom Lane wrote:
> Andres Freund <and...@anarazel.de> writes:
> > Explain is just a vehicle here, I admit that. But on what else should I
> > bolt it?
>
> If you don't like CREATE RULE, try having your test program send just
> Parse messages, and not Bind/Execute.
That sounds like a plan. Except that I would prefer to use pgbench. To avoid
the planning overhead...
I see it correctly that I would need to
I tpgbench is a more appropriate place to add such an option...
> If we do that, it will be a
> feature that we have to support forever, and possibly fix bugs in ---
> what if the system crashes because the rewriter wasn't invoked, for
> example?
rewrite=off aborts before planning, so that specific problem I don't see. The
name is rather bad I admit. Its mostly there to avoid the copyObject() which
skews results considerably:
* Because the rewriter and planner tend to scribble on the input, we
make
* a preliminary copy of the source querytree. This prevents problems
in
* the case that the EXPLAIN is in a portal or plpgsql function and is
* executed repeatedly. (See also the same hack in DECLARE CURSOR and
* PREPARE.) XXX FIXME someday.
*/
rewritten = QueryRewrite((Query *) copyObject(stmt->query));
> I still dislike the idea of
> exposing a fundamentally-broken-and-useless variant of EXPLAIN in order
> to have a test harness for a variant of performance testing that hardly
> anyone cares about. (There is no real-world case where the performance
> of the parser matters in isolation.)
I absolutely cannot agree on the fact that the speed parse-analyze is
irrelevant though. In several scenarios using prepared statements is not a
viable/simple option. Think transaction-level pooling for example. Or the
queries generated by all those ORMs out there.
When executing many small statments without prepared statments a rather large
portion of time is spent parsing.
Consider:
statement: EXPLAIN SELECT * FROM pg_class WHERE oid = 1000;
pgbench -M simple -f ~/.tmp/simple1.sql -T 3
tps = 16067.780407
pgbench -M prepared -f ~/.tmp/simple1.sql -T 3
tps = 24348.244630
In both variants the queries are fully planned as far as I can see. The only
difference there is parsing. I do find the difference in speed rather notable.
That does represent measurements from realworld profiles I gathered were
functions related to parsing + parse analysis contribute up to 1/3 of the
runtime.
Obviously nobody needs to benchmark the parser alone in a production scenario.
But if you want to improve the overall performance of some workload analysing
bits and pieces alone to get a useful, more detailed profile is a pretty sane
approach.
Why should that be a variant of performance testing that nobody cares about?
Andres
From c5251309a3eb3e826eab9bf792d4ba84fca63738 Mon Sep 17 00:00:00 2001
From: Andres Freund <and...@anarazel.de>
Date: Wed, 9 Nov 2011 17:52:38 +0100
Subject: [PATCH] Add new EXPLAIN options "plan" and "rewrite"
Those commands are useful to make it easier to gauge the costs of those
respective steps when introducing new features or optimizing existing code.
Another useful thing would be to be able to separate the parse and
parse-analyze steps, but that is more invasive.
---
src/backend/commands/explain.c | 56 +++++++++++++++++++++++++++++----------
src/include/commands/explain.h | 2 +
2 files changed, 43 insertions(+), 15 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index e38de5c..2987d3b 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -133,6 +133,10 @@ ExplainQuery(ExplainStmt *stmt, const char *queryString,
es.costs = defGetBoolean(opt);
else if (strcmp(opt->defname, "buffers") == 0)
es.buffers = defGetBoolean(opt);
+ else if (strcmp(opt->defname, "plan") == 0)
+ es.plan = defGetBoolean(opt);
+ else if (strcmp(opt->defname, "rewrite") == 0)
+ es.rewrite = defGetBoolean(opt);
else if (strcmp(opt->defname, "format") == 0)
{
char *p = defGetString(opt);
@@ -158,11 +162,25 @@ ExplainQuery(ExplainStmt *stmt, const char *queryString,
opt->defname)));
}
+
if (es.buffers && !es.analyze)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("EXPLAIN option BUFFERS requires ANALYZE")));
+ if ((!es.plan || !es.rewrite) && es.analyze)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("EXPLAIN option !PLAN contradicts ANALYZE")));
+ /*
+ * XXX: we could check for !plan && rewrite but that would mean !rewrite
+ * would have to explicitly specified when disabling planning...
+ */
+
+ /* emit opening boilerplate */
+ ExplainBeginOutput(&es);
+
+ Assert(IsA(stmt->query, Query));
/*
* Parse analysis was done already, but we still have to run the rule
* rewriter. We do not do AcquireRewriteLocks: we assume the query either
@@ -175,11 +193,13 @@ ExplainQuery(ExplainStmt *stmt, const char *queryString,
* executed repeatedly. (See also the same hack in DECLARE CURSOR and
* PREPARE.) XXX FIXME someday.
*/
- Assert(IsA(stmt->query, Query));
- rewritten = QueryRewrite((Query *) copyObject(stmt->query));
+ if(!es.rewrite){
+ if (es.format == EXPLAIN_FORMAT_TEXT)
+ appendStringInfoString(es.str, "not rewriting query because auf !rewrite\n");
+ goto out;
+ }
- /* emit opening boilerplate */
- ExplainBeginOutput(&es);
+ rewritten = QueryRewrite((Query *) copyObject(stmt->query));
if (rewritten == NIL)
{
@@ -189,22 +209,26 @@ ExplainQuery(ExplainStmt *stmt, const char *queryString,
*/
if (es.format == EXPLAIN_FORMAT_TEXT)
appendStringInfoString(es.str, "Query rewrites to nothing\n");
+ goto out;
}
- else
- {
- ListCell *l;
- /* Explain every plan */
- foreach(l, rewritten)
- {
- ExplainOneQuery((Query *) lfirst(l), &es, queryString, params);
+ if(!es.plan){
+ if (es.format == EXPLAIN_FORMAT_TEXT)
+ appendStringInfoString(es.str, "not planning or rewriting query because !plan\n");
+ goto out;
+ }
- /* Separate plans with an appropriate separator */
- if (lnext(l) != NULL)
- ExplainSeparatePlans(&es);
- }
+ /* Explain every plan */
+ foreach(lc, rewritten)
+ {
+ ExplainOneQuery((Query *) lfirst(lc), &es, queryString, params);
+
+ /* Separate plans with an appropriate separator */
+ if (lnext(lc) != NULL)
+ ExplainSeparatePlans(&es);
}
+out:
/* emit closing boilerplate */
ExplainEndOutput(&es);
Assert(es.indent == 0);
@@ -229,6 +253,8 @@ ExplainInitState(ExplainState *es)
/* Set default options. */
memset(es, 0, sizeof(ExplainState));
es->costs = true;
+ es->plan = true;
+ es->rewrite = true;
/* Prepare output buffer. */
es->str = makeStringInfo();
}
diff --git a/src/include/commands/explain.h b/src/include/commands/explain.h
index d7998c3..78c4e6d 100644
--- a/src/include/commands/explain.h
+++ b/src/include/commands/explain.h
@@ -31,6 +31,8 @@ typedef struct ExplainState
bool analyze; /* print actual times */
bool costs; /* print costs */
bool buffers; /* print buffer usage */
+ bool plan; /* plan the query */
+ bool rewrite; /* rewrite the query */
ExplainFormat format; /* output format */
/* other states */
PlannedStmt *pstmt; /* top of plan */
--
1.7.7.3
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
