X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 3E856D1B508 for ; Fri, 3 Oct 2003 21:54:33 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 50888-10 for ; Fri, 3 Oct 2003 18:53:48 -0300 (ADT) Received: from rlx13.zapatec.com (66-117-144-213.zapatec.lmi.net [66.117.144.213]) by svr1.postgresql.org (Postfix) with ESMTP id 967CED1B4FE for ; Fri, 3 Oct 2003 18:53:45 -0300 (ADT) Received: from rlx11.zapatec.com (rlx11.pr.zapatec.com [192.168.1.132]) by rlx13.zapatec.com (Postfix) with ESMTP id 413EFACAC for ; Fri, 3 Oct 2003 21:53:48 +0000 (GMT) Received: (from dror@localhost) by rlx11.zapatec.com (8.12.3/8.12.3/Submit) id h93Lrl0i097438 for pgsql-performance@postgresql.org; Fri, 3 Oct 2003 14:53:47 -0700 (PDT) (envelope-from dror) Date: Fri, 3 Oct 2003 14:53:47 -0700 From: Dror Matalon To: Postgresql Performance Subject: Re: Speeding up Aggregates Message-ID: <20031003215347.GQ87525@rlx11.zapatec.com> References: <20031003202120.GO87525@rlx11.zapatec.com> <1065217488.91586.3.camel@jester> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1065217488.91586.3.camel@jester> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/113 X-Sequence-Number: 3861 On Fri, Oct 03, 2003 at 05:44:49PM -0400, Rod Taylor wrote: > > item_max_date() looks like this: > > select max(dtstamp) from items where channel = $1 and link = $2; > > It is too bad the (channel, link) index doesn't have dtstamp at the end > of it, otherwise the below query would be a gain (might be a small one > anyway). > > select dtstamp > from items > where channel = $1 > and link = $2 > ORDER BY dtstamp DESC > LIMIT 1; Similar idea to what Josh suggested. I did create an additional index with dtstamp at the end and it doesn't look like the planner used it. Using the above query instead of max() didn't improve things either. > > > Could you show us the exact specification of the function? In > particular, did you mark it VOLATILE, IMMUTABLE, or STABLE? > > I hope it isn't the first or second one ;) CREATE or REPLACE FUNCTION item_max_date (int4, varchar) RETURNS timestamptz AS ' select max(dtstamp) from items where channel = $1 and link = $2; ' LANGUAGE 'sql'; -- Dror Matalon Zapatec Inc 1700 MLK Way Berkeley, CA 94709 http://www.zapatec.com