Re: Two Index Questions

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.

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.

--
-Josh BerkusAglio Database SolutionsSan Francisco