Re: Functional index problems. (Was: Many joins: monthly summaries S-L--O--W)

On Thursday, Oct 23, 2003, at 04:17 Asia/Tokyo, Tom Lane wrote:
> I'm not particularly happy with the phrase "expressional index", and
> would like to think of something else to use before the 7.4 docs go
> final. Any ideas?

Though it might be nice to be creative and name it something like
"Bill", for a defined language like SQL I think it's best to work
within the framework already used to describe the language. Looking in
the PostgreSQL docs (which I'm sure you've done), I find this
definition of "value expression"

A value expression is one of the following:
- A constant or literal value; see Section 1.1.2 .
- A column reference.
- A positional parameter reference, in the body of a function
declaration.
- An operator invocation.
- A function call.
- An aggregate expression.
- A type cast.
- A scalar subquery.
- Another value expression in parentheses, useful to group
subexpressions and override precedence.

Preceding this is a paragraph mentioning that value expressions are
also called "scalar expressions" or just "expressions". I was surprised
to find column references are included in this list, but thinking about
it, a column has a scalar value, which is what any function or operator
returns as well. According to the above description of "value
expression", a normal column index is just a special case of the more
general "expression" index.

(Actually, I'm a bit confused with the definition of value expression.
Does column reference here mean value in the same row (and same table)?
That's the only way I can see getting a scalar value. If it means
reference in the constraint meaning, it's a set of allowed values,
rather than a specific one, which definitely wouldn't work for an
index. Perhaps the use of the word "reference" here is inappropriate,
though I haven't looked through the rest of the documentation to see
how the term "column reference" is used. My bad, I know. I'll
definitely accept criticism for not doing my homework thoroughly.)

To me, an operator is just a special class of function that doesn't use
the more general func(arg [, arg]) construction. 2 + 2 could just as
easily be add(2,2) — and maybe it is already defined this way as well.
I haven't checked.

However, I can see that it might be useful to distinguish between
functions and column references. One term I thought about was "derived
index". Another was "evaluated index". But both of these terms seem to
introduce unnecessary language.

My final thought would be to continue using "functional index", noting
that operators are a special class of function. Out of the above
description of "value expression", I believe only functions and
operators are allowed as expressions in the CREATE INDEX syntax,
correct? (Besides referring to a column.)

What do you think?

Michael