From: Andriy Tkachuk <ant@imt.com.ua>
To: pgsql-performance@postgresql.org
Subject: Re: runtime of the same query in function differs on 2
Date: Fri, 3 Oct 2003 10:02:03 +0300 (EEST)
Message-ID: <20031003092552.B62657-100000@pool.imt.com.ua> (raw)
In-Reply-To: <3F7CBA4E.6020302@bigfoot.com>
No: the function is calc_total(int,int,int) and the table have the
same types.
As Tom said that my problem is because of planning in pl/pgsql. As
is written in
http://www.postgresql.org/docs/7.3/static/plpgsql.html#PLPGSQL-OVERVIEW
plans for queries in pl/pgsql are made just once - when they are
first used in function by backend. So AFAICS this planning do not
take into consideration table statistics because it don't see values
of variables in queries (or if see than it must not take them into account,
because they may be changed in future function callings).
I rollback to my previous realization of calc_total() on pl/tcl. I
use there spi_exec - so the query always regards as dynamic - it
always parsed, rewritten, planned but executes fastest much more
:)
On Fri, 3 Oct 2003, Gaetano Mendola wrote:
> Andriy Tkachuk wrote:
>
> > Hi folks.
> >
> > What's wrong with planner that executes my query in function?:
> > (i mean no explanation but runtime)
> >
> >
> > tele=# EXPLAIN analyze select calc_total(6916799, 1062363600, 1064955599);
> > QUERY PLAN
> > ------------------------------------------------------------------------------------------
> > Result (cost=0.00..0.01 rows=1 width=0) (actual time=36919.37..36919.37 rows=1 loops=1)
> > Total runtime: 36919.40 msec
> > ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> >
> > tele=# \df+ calc_total
> > ...
> > declare
> > usr alias for $1;
> > d1 alias for $2;
> > d2 alias for $3;
> > res integer;
> > begin
> > select sum(cost) into res
> > from bills where
> > (parent(user_id) = usr or user_id = usr)
> > and dat >= d1 and dat < d2;
> > if res is not null then
> > return res;
> > else
> > return 0;
> > end if;
> > end;
>
> You didn't wrote the type of d1 and d2, I had your same problem:
>
> declare
> a_user alias for $1;
> res INTEGER;
> begin
> select cost into res
> from my_table
> where login = a_user;
>
> ......
> end;
>
> the problem was that login was a VARCHAR and a_user was a TEXT so
> the index was not used, was enough cast a_user::varchar;
>
>
> I believe that your dat, d1, d2 are not "index" comparable.
>
>
> Gaetano
>
>
>
>
>
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: ant@imt.com.ua
Subject: Re: runtime of the same query in function differs on 2
In-Reply-To: <20031003092552.B62657-100000@pool.imt.com.ua>
References: <3F7CBA4E.6020302@bigfoot.com> <20031003092552.B62657-100000@pool.imt.com.ua>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox