Skip site navigation (1) Skip section navigation (2)

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

From: Andres Freund <andres(at)anarazel(dot)de>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: EXPLAIN (plan off, rewrite off) for benchmarking
Date: 2011-11-19 16:47:07
Message-ID: 201111191747.07918.andres@anarazel.de (view raw or flat)
Thread:
Lists: pgsql-hackers
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

Attachment: 0001-Add-new-EXPLAIN-options-plan-and-rewrite.patch
Description: text/x-patch (4.4 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2011-11-19 17:27:09
Subject: Singleton range constructors versus functional coercion notation
Previous:From: Greg SmithDate: 2011-11-19 16:44:46
Subject: Re: Core Extensions relocation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group