Re: Two Index Questions

On Fri, Jul 19, 2002 at 09:33:59 -0700,
Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> Folks (esp. Tom, Stephan, and Bruce):
>
> 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.