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(at)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(at)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(at)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(at)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