Re: EXPLAIN (plan off, rewrite off) for benchmarking - Mailing list pgsql-hackers
From | Andres Freund |
---|---|
Subject | Re: EXPLAIN (plan off, rewrite off) for benchmarking |
Date | |
Msg-id | 201111191747.07918.andres@anarazel.de Whole thread Raw |
In response to | Re: EXPLAIN (plan off, rewrite off) for benchmarking (Tom Lane <tgl@sss.pgh.pa.us>) |
Responses |
Re: EXPLAIN (plan off, rewrite off) for benchmarking
Re: EXPLAIN (plan off, rewrite off) for benchmarking |
List | pgsql-hackers |
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
pgsql-hackers by date: