Re: Have we out-grown Flex? - Mailing list pgsql-hackers

On Wed, May 2, 2012 at 9:31 AM, Peter Geoghegan <peter@2ndquadrant.com> wrote:
> On 2 May 2012 17:20, Jeff Janes <jeff.janes@gmail.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
get usec/parse)

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
planning) .

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.

Cheers,

Jeff