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 DC43ED1D841 for ; Wed, 25 Feb 2004 06:05:26 +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 39529-05 for ; Wed, 25 Feb 2004 02:05:24 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id D621ED1D432 for ; Wed, 25 Feb 2004 02:05:22 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i1P64mC2000939; Wed, 25 Feb 2004 01:04:49 -0500 (EST) To: Robert Creager Cc: PGPerformance Subject: Re: Speed up a function?CREATE TABLE readings ( "when" TIMESTAMP DEFAULT timeofday()::timestamp NOT NULL PRIMARY KEY, "barometer" FLOAT DEFAULT NULL, In-reply-to: <20040224221016.2c35e448.Robert_Creager@LogicalChaos.org> References: <20040224221016.2c35e448.Robert_Creager@LogicalChaos.org> Comments: In-reply-to Robert Creager message dated "Tue, 24 Feb 2004 22:10:16 -0700" Date: Wed, 25 Feb 2004 01:04:48 -0500 Message-ID: <938.1077689088@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/293 X-Sequence-Number: 5817 Robert Creager writes: > I've implemented a couple of functions ala date_trunc (listed at the bottom) > [ and they're too slow ] Well, it's hardly surprising that a function that invokes date_trunc and half a dozen other comparably-expensive operations should be half a dozen times as expensive as date_trunc. Not to mention that plpgsql is inherently far slower than C. Assuming that you don't want to descend to writing C, I'd suggest doing arithmetic on the Unix-epoch version of the timestamp. Perhaps something along the lines of select 'epoch'::timestamptz + trunc(extract(epoch from now())/(3600*24*7))*(3600*24*7) * '1sec'::interval; This doesn't have the same roundoff behavior as what you posted, but I think it could be adjusted to do so with a couple more additions and subtractions, unless there's some magic I'm not seeing about the year boundary behavior. Certainly the five-minute-trunc problem could be done this way. If you do feel like descending to C, I don't see any fundamental reason why we accept date_part('week',...) but not date_trunc('week',...). Feel free to submit a patch. regards, tom lane