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

On Saturday, November 19, 2011 04:52:10 PM Tom Lane wrote:
> Andres Freund <andres@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
portalor plpgsql function and is * executed repeatedly.  (See also the same hack in DECLARE CURSOR and * PREPARE.)  XXX
FIXMEsomeday.        */       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