pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andriy Tkachuk <ant@imt.com.ua>
To: pgsql-performance@postgresql.org
Subject: runtime of the same query in function differs on 2 degree!
Date: Thu, 2 Oct 2003 16:39:11 +0300 (EEST)
Message-ID: <20031002163005.N53420-100000@pool.imt.com.ua> (raw)

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.




view thread (4+ messages)  latest in thread

Message-ID: <20031002163005.N53420-100000@pool.imt.com.ua>
Permalink:  ../20031002163005.N53420-100000@pool.imt.com.ua/
Also on:    postgresql.org/message-id/20031002163005.N53420-100000@pool.imt.com.ua

 · 

reply

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 degree!
  In-Reply-To: <20031002163005.N53420-100000@pool.imt.com.ua>
  References: <20031002163005.N53420-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