Skip site navigation (1) Skip section navigation (2)

Re: Have we out-grown Flex?

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 (view raw or flat)
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

In response to

Responses

pgsql-hackers by date

Next:From: Magnus HaganderDate: 2012-05-02 17:42:47
Subject: Re: Have we out-grown Flex?
Previous:From: Jameison MartinDate: 2012-05-02 17:20:13
Subject: Re: patch submission: truncate trailing nulls from heap rows to reduce the size of the null bitmap

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group