runtime of the same query in function differs on 2 degree!

From: Andriy Tkachuk <ant(at)imt(dot)com(dot)ua>
To: pgsql-performance(at)postgresql(dot)org
Subject: runtime of the same query in function differs on 2 degree!
Date: 2003-10-02 13:39:11
Message-ID: 20031002163005.N53420-100000@pool.imt.com.ua
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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;

tele=# EXPLAIN analyze select sum(cost) from bills where (parent(user_id) = 6916799 or user_id = 6916799) and dat >= 1062363600 and dat < 10649555
99;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
------------------
Aggregate (cost=17902.80..17902.80 rows=1 width=4) (actual time=101.04..101.04 rows=1 loops=1)
-> Index Scan using bills_parent_user_id_idx, bills_userid_dat_idx on bills (cost=0.00..17901.11 rows=679 width=4) (actual time=101.03..101.0
3 rows=0 loops=1)
Index Cond: ((parent(user_id) = 6916799) OR ((user_id = 6916799) AND (dat >= 1062363600) AND (dat < 1064955599)))
Filter: (((parent(user_id) = 6916799) OR (user_id = 6916799)) AND (dat >= 1062363600) AND (dat < 1064955599))
Total runtime: 101.14 msec
^^^^^^^^^^^^^^^^^^^^^^^^^^

So the query is the same as in calc_total(usr,d1,d2) function,
but execute time extremely differs.

Is it normal?

Thanks,
Andriy Tkachuk.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Oleg Lebedev 2003-10-02 14:29:52 Re: TPC-R benchmarks
Previous Message Ron Johnson 2003-10-02 09:28:43 basket, eggs & NAS (was eggs Re: Ideal Hardware?)