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

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