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 D2D65D1B50A for ; Wed, 8 Oct 2003 14:55:15 +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 72925-05 for ; Wed, 8 Oct 2003 11:54:30 -0300 (ADT) Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194]) by svr1.postgresql.org (Postfix) with ESMTP id C1420D1B4F0 for ; Wed, 8 Oct 2003 11:54:25 -0300 (ADT) Received: from stark.dyndns.tv (gsstark.mtl.istop.com [66.11.160.162]) by smtp.istop.com (Postfix) with ESMTP id 3B3FA369F8; Wed, 8 Oct 2003 10:54:25 -0400 (EDT) Received: from localhost ([127.0.0.1] helo=stark.dyndns.tv ident=foobar) by stark.dyndns.tv with smtp (Exim 3.36 #1 (Debian)) id 1A7Fho-0008Lz-00; Wed, 08 Oct 2003 10:54:24 -0400 To: Rod Taylor Cc: Dror Matalon , Postgresql Performance Subject: Re: Speeding up Aggregates References: <20031003202120.GO87525@rlx11.zapatec.com> <1065217488.91586.3.camel@jester> <20031003215347.GQ87525@rlx11.zapatec.com> <1065219028.91586.8.camel@jester> In-Reply-To: <1065219028.91586.8.camel@jester> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 08 Oct 2003 10:54:24 -0400 Message-ID: <87llrvbx0f.fsf@stark.dyndns.tv> Lines: 33 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/205 X-Sequence-Number: 3953 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