Re: Upgrading to 7.2

Chris Field <cfields(at)affinitysolutions(dot)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