X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id BEBCFD1B4FB for ; Thu, 2 Oct 2003 13:40:06 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 93668-06 for ; Thu, 2 Oct 2003 10:39:20 -0300 (ADT) Received: from pool.imt.com.ua (office.imt.com.ua [212.109.53.33]) by svr1.postgresql.org (Postfix) with ESMTP id 85C1BD1B4EE for ; Thu, 2 Oct 2003 10:39:14 -0300 (ADT) Received: from pool.imt.com.ua (localhost [127.0.0.1]) by pool.imt.com.ua (8.12.8p2/8.12.8) with ESMTP id h92DdDlB063937 for ; Thu, 2 Oct 2003 16:39:14 +0300 (EEST) (envelope-from ant@imt.com.ua) Received: from localhost (ant@localhost) by pool.imt.com.ua (8.12.8p2/8.12.8/Submit) with ESMTP id h92DdBiB063934 for ; Thu, 2 Oct 2003 16:39:13 +0300 (EEST) (envelope-from ant@imt.com.ua) X-Authentication-Warning: pool.imt.com.ua: ant owned process doing -bs Date: Thu, 2 Oct 2003 16:39:11 +0300 (EEST) From: Andriy Tkachuk To: pgsql-performance@postgresql.org Subject: runtime of the same query in function differs on 2 degree! Message-ID: <20031002163005.N53420-100000@pool.imt.com.ua> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/47 X-Sequence-Number: 3795 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.