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 D5722D1B554 for ; Wed, 8 Oct 2003 18:21:14 +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 18749-04 for ; Wed, 8 Oct 2003 15:20:26 -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 24C18D1B583 for ; Wed, 8 Oct 2003 15:18:24 -0300 (ADT) Received: from rlx11.zapatec.com (rlx11.pr.zapatec.com [192.168.1.132]) by rlx13.zapatec.com (Postfix) with ESMTP id CC395ACC3 for ; Wed, 8 Oct 2003 18:18:19 +0000 (GMT) Received: (from dror@localhost) by rlx11.zapatec.com (8.12.3/8.12.3/Submit) id h98IIJFc011621 for pgsql-performance@postgresql.org; Wed, 8 Oct 2003 11:18:19 -0700 (PDT) (envelope-from dror) Date: Wed, 8 Oct 2003 11:18:19 -0700 From: Dror Matalon To: Postgresql Performance Subject: Re: Speeding up Aggregates Message-ID: <20031008181819.GJ2979@rlx11.zapatec.com> References: <20031003202120.GO87525@rlx11.zapatec.com> <1065217488.91586.3.camel@jester> <20031003215347.GQ87525@rlx11.zapatec.com> <1065219028.91586.8.camel@jester> <87llrvbx0f.fsf@stark.dyndns.tv> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <87llrvbx0f.fsf@stark.dyndns.tv> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/231 X-Sequence-Number: 3979 Actually what finally sovled the problem is repeating the dtstamp > last_viewed in the sub select select articlenumber, channel, description, title, link, dtstamp from items i1, my_channels where ((i1.channel = '22222' and my_channels.id = '22222' and owner = 'drormata' and (dtstamp > last_viewed)) ) and (dtstamp = (select max (dtstamp) from items i2 where channel = '22222' and i1.link = i2.link)); to explain analyze select articlenumber, channel, description, title, link, dtstamp from items i1, my_channels where ((i1.channel = '22222' and my_channels.id = '22222' and owner = 'drormata' and (dtstamp > last_viewed)) ) and (dtstamp = (select max (dtstamp) from items i2 where channel = '22222' and i1.link = i2.link and dtstamp > last_viewed)); Which in the stored procedure looks like this: CREATE or REPLACE FUNCTION item_max_date (int4, varchar, timestamptz) RETURNS timestamptz AS ' select max(dtstamp) from items where channel = $1 and link = $2 and dtstamp > $3; ' LANGUAGE 'sql'; Basically I have hundreds or thousands of items but only a few that satisfy "dtstamp > last_viewed". Obviously I want to run the max() only on on a few items. Repeating "dtstamp > last_viewed" did the trick, but it seems like there should be a more elegant/clear way to tell the planner which constraint to apply first. Dror On Wed, Oct 08, 2003 at 10:54:24AM -0400, Greg Stark wrote: > Rod Taylor writes: > > > On Fri, 2003-10-03 at 17:53, Dror Matalon wrote: > > > On Fri, Oct 03, 2003 at 05:44:49PM -0400, Rod Taylor wrote: > > > > > > > > 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. > > You need to do: > > ORDER BY channel DESC, link DESC, dtstamp DESC > > This is an optimizer nit. It doesn't notice that since it selected on channel > and link already the remaining tuples in the index will be ordered simply by > dtstamp. > > (This is the thing i pointed out previously in > <87el6ckrlu.fsf@stark.dyndns.tv> on Feb 13th 2003 on pgsql-general) > > > -- > greg > -- Dror Matalon Zapatec Inc 1700 MLK Way Berkeley, CA 94709 http://www.zapatec.com