Speed up a function?CREATE TABLE readings ( "when" TIMESTAMP DEFAULT timeofday()::timestamp NOT NULL PRIMARY KEY, "barometer" FLOAT DEFAULT NULL,

--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'--