From: | Gaetano Mendola <mendola(at)bigfoot(dot)com> |
---|---|
To: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Cc: | Andriy Tkachuk <ant(at)imt(dot)com(dot)ua> |
Subject: | Re: runtime of the same query in function differs on 2 degree! |
Date: | 2003-10-02 23:52:46 |
Message-ID: | 3F7CBA4E.6020302@bigfoot.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Christopher Browne | 2003-10-03 02:08:18 | Re: count(*) slow on large tables |
Previous Message | scott.marlowe | 2003-10-02 23:35:39 | Re: further testing on IDE drives |