X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 3D28FD1B860 for ; Wed, 25 Feb 2004 05:11:01 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 31759-01 for ; Wed, 25 Feb 2004 01:10:58 -0400 (AST) Received: from srvr3.iniquinet.com (srvr2.iniquinet.com [64.240.87.12]) by svr1.postgresql.org (Postfix) with SMTP id D52BDD1B4AA for ; Wed, 25 Feb 2004 01:10:56 -0400 (AST) Received: (qmail 27279 invoked by uid 104); 25 Feb 2004 04:54:56 -0000 Received: from Robert_Creager@LogicalChaos.org by srvr3.iniquinet.com by uid 101 with qmail-scanner-1.15 (clamscan: 0.65. spamassassin: 2.55. Clear:SA:0(-5.3/6.0):. Processed in 32.277182 secs); 25 Feb 2004 04:54:56 -0000 Received: from unknown (HELO thunder.mshome.net) (216.58.165.145) by srvr3.iniquinet.com with SMTP; 25 Feb 2004 04:54:23 -0000 Received: from localhost.localdomain (thunder.mshome.net [192.168.0.250]) by thunder.mshome.net (Postfix) with ESMTP id 414CFE413 for ; Tue, 24 Feb 2004 22:10:22 -0700 (MST) Received: from logicalchaos.org (thunder.mshome.net [192.168.0.250]) by thunder.mshome.net (Postfix) with SMTP id C509387DC2 for ; Tue, 24 Feb 2004 22:10:16 -0700 (MST) Date: Tue, 24 Feb 2004 22:10:16 -0700 From: Robert Creager To: PGPerformance Subject: Speed up a function?CREATE TABLE readings ( "when" TIMESTAMP DEFAULT timeofday()::timestamp NOT NULL PRIMARY KEY, "barometer" FLOAT DEFAULT NULL, Message-Id: <20040224221016.2c35e448.Robert_Creager@LogicalChaos.org> Organization: Starlight Vision, LLC. X-Mailer: Sylpheed version 0.9.5claws (GTK+ 1.2.10; i586-mandrake-linux-gnu) Mime-Version: 1.0 Content-Type: multipart/signed; protocol="application/pgp-signature"; micalg="pgp-sha1"; boundary="Multipart_Tue__24_Feb_2004_22_10_16_-0700_TQy=.0qAZbm6_2k'" SUBJ_HAS_SPACES,USER_IN_WHITELIST autolearn=no version=2.63 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/292 X-Sequence-Number: 5816 --Multipart_Tue__24_Feb_2004_22_10_16_-0700_TQy=.0qAZbm6_2k' Content-Type: text/plain; charset=US-ASCII Content-Disposition: inline Content-Transfer-Encoding: 7bit Hey All, I've implemented a couple of functions ala date_trunc (listed at the bottom). These functions are executed every 5 minutes (date_trunc_minute) and every week (date_trunc_week) across 16 different values. The problem is that they take way too long to execute (nearly 7x the 'regular' date_trunc function). What might be the best way to fix the problem? Use a different function language? Re-write the functions? Re-write the queries? The gist of what I'm doing is filling schema tables with weather summary information for the time period in question. Currently I don't have indexes on these tables. Would indexs on readings."when" and minute.barometer."time" be used with date_trunc? Functional indexes maybe? CREATE TABLE readings ( "when" TIMESTAMP DEFAULT timeofday()::timestamp NOT NULL PRIMARY KEY, "barometer" FLOAT DEFAULT NULL, ); CREATE SCHEMA minute; CREATE TABLE minute.barometer ( "time" TIMESTAMP NOT NULL, min_reading FLOAT NOT NULL, max_reading FLOAT NOT NULL, avg_reading FLOAT NOT NULL ); The "hour" schema is identical to the "minute" schema. weather=# EXPLAIN ANALYZE weather-# SELECT p.period, p.min, p.max, p.avg weather-# FROM (SELECT date_trunc_minute( 'minute'::text, "when" ) AS period, weather(# min( barometer ), max( barometer ), avg( barometer ) weather(# FROM readings weather(# WHERE barometer NOTNULL weather(# GROUP BY period) AS p weather-# WHERE p.period weather-# NOT IN (SELECT "time" FROM minute.barometer ) weather-# AND p.period != date_trunc_minute( 'minute'::text, now()::timestamp ); QUERY PLAN ------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------- Subquery Scan p (cost=1665.63..2282.47 rows=13708 width=32) (actual time=3318.758..3318.758 rows=0 loops=1) Filter: (NOT (hashed subplan)) -> HashAggregate (cost=1501.61..1775.76 rows=27415 width=16) (actual time=3227.409..3263.367 rows=13918 loops=1) -> Seq Scan on readings (cost=0.00..1227.46 rows=27415 width=16) (actual time=1.075..3028.673 rows =69398 loops=1) Filter: ((barometer IS NOT NULL) AND (date_trunc_minute('minute'::text, "when") <> date_trunc_ minute('minute'::text, (now())::timestamp without time zone))) SubPlan -> Seq Scan on barometer (cost=0.00..144.02 rows=8002 width=8) (actual time=0.008..15.406 rows=13918 l oops=1) Total runtime: 3320.146 ms (8 rows) weather=# EXPLAIN ANALYZE weather-# SELECT p.period, p.min, p.max, p.avg weather-# FROM (SELECT date_trunc( 'hour'::text, "when" ) AS period, weather(# min( barometer ), max( barometer ), avg( barometer ) weather(# FROM readings weather(# WHERE barometer NOTNULL weather(# GROUP BY period) AS p weather-# WHERE p.period weather-# NOT IN (SELECT "time" FROM hour.barometer ) weather-# AND p.period != date_trunc( 'hour'::text, now()::timestamp ); QUERY PLAN ------------------------------------------------------------------------------------------------------------- --------------------------------------------- Subquery Scan p (cost=1524.11..2140.95 rows=13708 width=32) (actual time=551.516..551.516 rows=0 loops=1) Filter: (NOT (hashed subplan)) -> HashAggregate (cost=1501.61..1775.76 rows=27415 width=16) (actual time=544.859..547.605 rows=1173 loo ps=1) -> Seq Scan on readings (cost=0.00..1227.46 rows=27415 width=16) (actual time=0.596..399.344 rows= 69353 loops=1) Filter: ((barometer IS NOT NULL) AND (date_trunc('hour'::text, "when") <> date_trunc('hour'::t ext, (now())::timestamp without time zone))) SubPlan -> Seq Scan on barometer (cost=0.00..20.00 rows=1000 width=8) (actual time=0.007..1.268 rows=1173 loop s=1) Total runtime: 552.137 ms CREATE FUNCTION date_trunc_week( text, timestamp ) RETURNS timestamp AS ' DECLARE reading_time ALIAS FOR $2; year timestamp; dow integer; adjust text; week text; BEGIN year := date_trunc( ''year''::text, reading_time ); week := date_part( ''week'', reading_time ) - 1 || '' week''; dow := date_part( ''dow'', year ); -- If the dow is less than Thursday, then the start week is last year IF dow <= 4 THEN adjust := 1 - dow || '' day''; ELSE adjust := 8 - dow || '' day''; END IF; RETURN year + adjust::interval + week::interval; END; ' LANGUAGE plpgsql IMMUTABLE STRICT; CREATE OR REPLACE FUNCTION date_trunc_minute( text, timestamp ) RETURNS timestamp AS ' DECLARE reading_time ALIAS FOR $2; hour timestamp; adjust text; BEGIN hour := date_trunc( ''hour''::text, reading_time ); adjust := 5*trunc(date_part( ''minute'', reading_time ) / 5) || '' minute''; RETURN hour + adjust::interval; END; ' LANGUAGE plpgsql IMMUTABLE STRICT; Thanks, Rob -- 21:52:26 up 10 days, 5:33, 3 users, load average: 2.36, 2.40, 2.17 Linux 2.4.21-0.13_test #60 SMP Sun Dec 7 17:00:02 MST 2003 --Multipart_Tue__24_Feb_2004_22_10_16_-0700_TQy=.0qAZbm6_2k' Content-Type: application/pgp-signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.2 (GNU/Linux) iEYEARECAAYFAkA8LjgACgkQLQ/DKuwDYzkuXgCfdKJ2+ye2dlu/G98k8SRWkbrp 1ogAmwZhzeL+fveZ1c+0UZzjYDYZy7xJ =v9Vf -----END PGP SIGNATURE----- --Multipart_Tue__24_Feb_2004_22_10_16_-0700_TQy=.0qAZbm6_2k'--