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 16871D1BB71 for ; Fri, 9 Jan 2004 17:16:06 +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 38574-08 for ; Fri, 9 Jan 2004 13:15:34 -0400 (AST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id AB644D1DDA8 for ; Fri, 9 Jan 2004 11:38:12 -0400 (AST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id E36E335566; Fri, 9 Jan 2004 07:38:01 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id E1DB23539B; Fri, 9 Jan 2004 07:38:01 -0800 (PST) Date: Fri, 9 Jan 2004 07:38:01 -0800 (PST) From: Stephan Szabo To: Richard Huxton Cc: Dennis =?iso-8859-1?q?Bj=F6rklund?= , Bradley Tate , pgsql-performance@postgresql.org Subject: Re: Slow query problem In-Reply-To: <200401090919.04718.dev@archonet.com> Message-ID: <20040109073400.D69748@megazone.bigpanda.com> References: <200401090919.04718.dev@archonet.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=X-UNKNOWN Content-Transfer-Encoding: QUOTED-PRINTABLE X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/101 X-Sequence-Number: 5276 On Fri, 9 Jan 2004, Richard Huxton wrote: > 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 plan= ner > 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. AFAICS, yes it does take that effect into account (as best it can with the estimates).