Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Bruno Wolff III <bruno@wolff.to>
Subject: Re: Two Index Questions
Date: Fri, 19 Jul 2002 10:28:18 -0700
Cc: pgsql-sql@postgresql.org
References: <200207190933.59283.josh@agliodbs.com>
	<20020719170000.GB13657@wolff.to>
In-Reply-To: <20020719170000.GB13657@wolff.to>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200207191028.18547.josh@agliodbs.com>


Bruno,

> > I have two questions for my "Adventures in PostgreSQL" article reasearc=
h:
> >=20
> > 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=20
indexes=20
> > on A, B and C?   I've run a few tests, but I don't have enough data in =
the=20
> > seperate tables to really get a feel for the difference.
>=20
> If there are lots of G entries for fixed As, Bs and Cs, then another opti=
on
> 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.

I generally try to avoid using LIMIT, as it is a non-SQL92 extension.  Also=
,=20
LIMIT in subqueries might someday be disallowed as it interferes with the=
=20
fundmentally unordered nature of subqueries.

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco