Received: from wolff.to (wolff.to [66.92.219.49]) by postgresql.org (Postfix) with SMTP id F376347598F for ; Fri, 19 Jul 2002 13:50:47 -0400 (EDT) Received: (qmail 14921 invoked by uid 500); 19 Jul 2002 17:56:38 -0000 Date: Fri, 19 Jul 2002 12:56:38 -0500 From: Bruno Wolff III To: Josh Berkus Cc: pgsql-sql@postgresql.org Subject: Re: Two Index Questions Message-ID: <20020719175638.GA14836@wolff.to> Mail-Followup-To: Josh Berkus , pgsql-sql@postgresql.org References: <200207190933.59283.josh@agliodbs.com> <20020719170000.GB13657@wolff.to> <200207191028.18547.josh@agliodbs.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <200207191028.18547.josh@agliodbs.com> User-Agent: Mutt/1.3.25i X-Archive-Number: 200207/305 X-Sequence-Number: 8683 On Fri, Jul 19, 2002 at 10:28:18 -0700, Josh Berkus wrote: > > Bruno, > > > > I have two questions for my "Adventures in PostgreSQL" article reasearch: > > > > > > Multi-Column Indexes and GROUP BY: > > > Q: If you group a table by multiple colums, e.g. > > > SELECT t1.A, t1.B, t1.C, MAX(t1.G) > > > FROM t1 > > > GROUP BY t1.A, t1.B, t1.C > > > Then would a multi-column index on A, B, C be faster than seperate > indexes > > > on A, B and C? I've run a few tests, but I don't have enough data in the > > > seperate tables to really get a feel for the difference. > > > > If there are lots of G entries for fixed As, Bs and Cs, then another option > > would be to have an index on all 4 tables and use a subquery with a limit 1 > > clause to get the row with the max G value for any A, B and C. > > We're talking about only one table, here. Not four. Typo. I meant to say columns. The issue is that max doesn't use an index, but if there are a lot of different values of G for a given A, B and C, it may be better to use an index then to search through the applicable rows to find the maximum. > I generally try to avoid using LIMIT, as it is a non-SQL92 extension. Also, > LIMIT in subqueries might someday be disallowed as it interferes with the > fundmentally unordered nature of subqueries. I can see not using it because it is nonstandard. I don't think it would be disabled for the reason you have given. The documentation makes it pretty clear that you need to use order by if you want a particular tuple.