Re: EXPLAIN (plan off, rewrite off) for benchmarking

On Saturday, November 19, 2011 04:52:10 PM Tom Lane wrote:
> Andres Freund <andres(at)anarazel(dot)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