From: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
---|---|
To: | Peter Geoghegan <peter(at)2ndquadrant(dot)com> |
Cc: | PG Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Have we out-grown Flex? |
Date: | 2012-05-02 17:37:58 |
Message-ID: | CAMkU=1yYeLEcYpy83r2x=PmLX3OwPp9JKMPs4jc5=Wi-Gx0RpQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
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
From | Date | Subject | |
---|---|---|---|
Next Message | Magnus Hagander | 2012-05-02 17:42:47 | Re: Have we out-grown Flex? |
Previous Message | Jameison Martin | 2012-05-02 17:20:13 | Re: patch submission: truncate trailing nulls from heap rows to reduce the size of the null bitmap |