X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id A6A2FD1B454 for ; Fri, 9 Jan 2004 09:19:57 +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 76501-06 for ; Fri, 9 Jan 2004 05:19:09 -0400 (AST) Received: from anchor-post-34.mail.demon.net (anchor-post-34.mail.demon.net [194.217.242.92]) by svr1.postgresql.org (Postfix) with ESMTP id 202EAD1B466 for ; Fri, 9 Jan 2004 05:19:07 -0400 (AST) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-34.mail.demon.net with esmtp (Exim 3.35 #1) id 1AesnM-0002bV-0Y; Fri, 09 Jan 2004 09:19:08 +0000 Received: by mainbox.archonet.com (Postfix, from userid 529) id 3579217972; Fri, 9 Jan 2004 09:19:07 +0000 (GMT) Received: from client17.archonet.com (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 4F6EA17969; Fri, 9 Jan 2004 09:19:05 +0000 (GMT) From: Richard Huxton To: Dennis =?iso-8859-1?q?Bj=F6rklund?= Subject: Re: Slow query problem Date: Fri, 9 Jan 2004 09:19:04 +0000 User-Agent: KMail/1.5 Cc: Bradley Tate , References: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <200401090919.04718.dev@archonet.com> X-Bogosity: No, tests=bogofilter, spamicity=0.000000, version=0.15.3 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/98 X-Sequence-Number: 5273 On Friday 09 January 2004 08:57, Dennis Bj=F6rklund wrote: > On Fri, 9 Jan 2004, Richard Huxton wrote: > > > > select invheadref, invprodref, sum(units) > > > > from invtran > > > > group by invheadref, invprodref > > > > > > For the above query, shouldn't you have one index for both columns > > > (invheadref, invprodref). Then it should not need to sort at all to do > > > the grouping and it should all be fast. > > > > Not sure if that would make a difference here, since the whole table is > > being read. > > The goal was to avoid the sorting which should not be needed with that > index (I hope). So I still think that it would help in this case. Sorry - not being clear. I can see how it _might_ help, but will the planne= r=20 take into account the fact that even though: index-cost > seqscan-cost that (index-cost + no-sorting) < (seqscan-cost + sort-cost) assuming of course, that the costs turn out that way. --=20 Richard Huxton Archonet Ltd