Re: Upgrading to 7.2

Chris Field <cfields@affinitysolutions.com> writes:

> Thanks for responding, I am thinking it might be fairly beneficial to
> upgrade.
> 
> > Ummm, max() is an aggregate function, how can you create an index on
> > it? 
> 
> In the postgresSQL Essential Reference by Barry Stinson it specifically
> has a index topic on functional indexes, with the given example being
> " CREATE INDEX max_payroll_idx ON payroll (MAX(salary)); "

This just seems wrong.  MAX() is a function, not of a single value,
but of a set of values from a single column (ie it's an aggregate
function).  Think about what an index is, and I think you'll see that
you can't build one on based on an aggregate function.  It's not a
well-defined concept.

Think of it this way--an index is "a list of rows, organized by the
value of the index expression for each row."  An aggregate function
like MAX() or SUM() doesn't have a useful value for a single row--it's
only meaningful in the context of a set of rows.

Non-aggregate functions (ie most of them, like sqrt(), sin(), cos()
etc) can definitely be used in indexes.

> so either the book was a waste of money, or this is a 7.2 specific
> feature.

The author does seem confused about this point, but the book still
might be worthwhile--haven't read it myself. 

I might be totally out in left field here, but the reasoning above
makes sense to me at least.  ;)

-Doug
-- 
Let us cross over the river, and rest under the shade of the trees.  --T. J. Jackson, 1863