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
