On Thu, Feb 28, 2002 at 06:21:34PM +0200, Hannu Krosing wrote:
> On Thu, 2002-02-28 at 15:58, Karel Zak wrote:
> > On Thu, Feb 28, 2002 at 02:39:47PM +0100, Jean-Paul ARGUDO wrote:
> >
> > > 2) if prepared statments and stored execution plan exist, why can't thos be used
> > > from any client interface or simple sql?
> >
> > There is "execute already parsed query plan" in SPI layout only.
> > The PostgreSQL hasn't SQL interface for this -- except my experimental
> > patch for 7.0 (I sometime think about port it to latest PostgreSQL
> > releace, but I haven't relevant motivation do it...)
>
> I did some testing
>
> 5000*20 runs of update on non-existing key
>
> (send query+parse+optimise+update 0 rows)
>
> [hannu@taru abench]$ time ./abench.py 2>/dev/null
>
> real 0m38.992s
> user 0m6.590s
> sys 0m1.860s
>
> 5000*20 runs of update on random existing key
>
> (send query+parse+optimise+update 1 row)
>
> [hannu@taru abench]$ time ./abench.py 2>/dev/null
>
> real 1m48.380s
> user 0m17.330s
> sys 0m2.940s
>
>
> the backend wallclock time for first is 39.0 - 6.6 = 32.4
> the backend wallclock time for second is 108.4 - 17.3 = 91.1
>
> so roughly 1/3 of time is spent on
>
> communication+parse+optimize+locate
>
> and 2/3 on actually updating the tuples
>
> if we could save half of parse/optimise time by saving query plans, then
> the backend performance would go up from 1097 to 100000/(91.1-16.2)=1335
> updates/sec.
It depend on proportion between time-in-parser and time-in-executor. Ifyour query spend a lot of time in parser and
optimizeris a query plan cache interesting for you. Because the PostgreSQL has dynamic functions and operators the time
inparser can be for some queries very interesting.We have good notion about total queries time now (for example
frombenchtests), but we haven't real time statistics about path-of-queryin backend. How long time spend a query in the
parser,how log in the optimizer or executor? (... maybe use profiling, but I not surewith it). All my suggestion for
memorymanagment was based on resultof control messages those I wrote into mmgr. And for example Tom was surprised of
oftenrealloc usage. I want say, we need more and moredata from code, else we can't good optimize it ;-)suggestion:
"TODO:solid path-of-query time profiling for developers" :-) Karel
-- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/C, PostgreSQL, PHP, WWW, http://docs.linux.cz,
http://mape.jcu.cz
