On Wed, May 2, 2012 at 9:31 AM, Peter Geoghegan <peter(at)2ndquadrant(dot)com> wrote:
> On 2 May 2012 17:20, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>> For -S -M simple, the time spent planning is 5 times more than the
>> time spent parsing. It may be worthwhile to reduce the time spent
>> parsing, but if the goal is parity with MySQL it probably isn't the
>> place to start.
> Could you please share your figures and methodology? I've heard of far
> larger proportions than that.
I used two methods. One was to hack exec_simple_query so that, under
the control of a new GUC, it would do things such as pg_parse_query
the query 101 times, throwing away the results of the first 100 before
proceeding to use the 101 parse result as normal. Then I just run
pgbench under both settings, take the difference in 1/TPS between them
and divide by 100 to get the seconds per parse (and multiple by 1e6 to
I did the same thing for pg_analyze_and_rewrite, and for
pg_analyze_and_rewrite+pg_plan_queries (pg_plan_queries scribbles on
the structure produced by pg_analyze_and_rewrite, so you have to
repeat both as a unit, and then subtract the the
pg_analyze_and_rewrite timings off afterwards to isolate just the
On my current laptop and rebased to git HEAD, I got
2usec/pg_parse_query, 2usec/pg_analyze_and_rewrite, and
12usec/pg_plan_queries. Since my laptop is dual core, I did this
with -c2 -j2.
Back when I originally implemented and tested this on much older
hardware and about one year older pgsql code base, the absolute values
of usec/action were several fold higher, but the ratios of 1:1:6 were
about the same.
This does risk that it will overlook caching effects by repeating the
same thing in a tight loop. I.e. parses 2 through 101 might be much
faster than parse 1 was. Also, it risks that I simply don't know what
I'm doing and my attempts to throw away the results of a parse are
misbegotten--I just overwrote the old pointer with the new one and
assume the memory context would clean up the resulting orphans.
I could try to clean up and post the patch that implements this if you want.
The second method was just to do --enable-profiling on a stock build
and look at the call graph section of gprof output. It attributed 50%
to pg_plan_queries and children and about 10% to each of
pg_parse_query and pg_analyze_and_rewrite (including their respective
children). I don't put tremendous faith in gprof's call graph, but
the fact that the results are in agreement with the other method gives
me more confidence in both.
In response to
pgsql-hackers by date
|Next:||From: Magnus Hagander||Date: 2012-05-02 17:42:47|
|Subject: Re: Have we out-grown Flex?|
|Previous:||From: Jameison Martin||Date: 2012-05-02 17:20:13|
|Subject: Re: patch submission: truncate trailing nulls from heap rows to reduce the size of the null bitmap|