Re: Two Index Questions

On Fri, Jul 19, 2002 at 10:28:18 -0700, Josh Berkus <josh@agliodbs.com> 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.