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
pgpPnNfOk1KxP.pgp
Description: PGP signature