On Wed, May 2, 2012 at 9:31 AM, Peter Geoghegan <pe...@2ndquadrant.com> wrote: > On 2 May 2012 17:20, Jeff Janes <jeff.ja...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
