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 CA336D1B508 for ; Fri, 3 Oct 2003 22:17:42 +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 52458-08 for ; Fri, 3 Oct 2003 19:16:59 -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 7BBB1D1B500 for ; Fri, 3 Oct 2003 19:16:56 -0300 (ADT) Received: from rlx11.zapatec.com (rlx11.pr.zapatec.com [192.168.1.132]) by rlx13.zapatec.com (Postfix) with ESMTP id 3CD79ACAC for ; Fri, 3 Oct 2003 22:16:59 +0000 (GMT) Received: (from dror@localhost) by rlx11.zapatec.com (8.12.3/8.12.3/Submit) id h93MGwh2097525 for pgsql-performance@postgresql.org; Fri, 3 Oct 2003 15:16:58 -0700 (PDT) (envelope-from dror) Date: Fri, 3 Oct 2003 15:16:58 -0700 From: Dror Matalon To: Postgresql Performance Subject: Re: Speeding up Aggregates Message-ID: <20031003221658.GS87525@rlx11.zapatec.com> References: <20031003202120.GO87525@rlx11.zapatec.com> <1065217488.91586.3.camel@jester> <20031003215347.GQ87525@rlx11.zapatec.com> <1065219028.91586.8.camel@jester> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1065219028.91586.8.camel@jester> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/117 X-Sequence-Number: 3865 On Fri, Oct 03, 2003 at 06:10:29PM -0400, Rod Taylor wrote: > On Fri, 2003-10-03 at 17:53, Dror Matalon wrote: > > 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; > > It didn't make a difference even with the 3 term index? I guess you > don't have very many common values for channel / link combination. There's no noticeable difference between two term and three term indexes. > > > > How about the below? Note the word STABLE on the end. > > 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' STABLE; Made no difference. -- Dror Matalon Zapatec Inc 1700 MLK Way Berkeley, CA 94709 http://www.zapatec.com