Re: Oracle vs PostgreSQL in real life

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.

------------------

As an ad hoc test for parsing-planning-optimising costs I did the
following

backend time for  "explain update t01 set val='bum'"
30.0 - 5.7 = 24.3

[hannu@taru abench]$ time ./abench.py 2>/dev/null 

real    0m30.038s
user    0m5.660s
sys     0m2.800s


backend time for "explain update t01 set val='bum' where i = %s"
39.8 - 8.0 = 31.8

[hannu@taru abench]$ time ./abench.py 2>/dev/null 

real    0m39.883s
user    0m8.000s
sys     0m2.620s


so adding "where i=n" to a query made
(parse+plan+show plan) run 1.3 times slower

some of it must be communication overhead, but sure 
some is parsing/planning/optimising time.

--------------
Hannu