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

From Tom Lane
Subject Re: Functional index problems. (Was: Many joins: monthly summaries S-L--O--W)
Date
Msg-id 25982.1066877638@sss.pgh.pa.us
Whole thread Raw
In response to Re: Functional index problems. (Was: Many joins: monthly summaries S-L--O--W)  (Michael Glaesmann <grzm@myrealbox.com>)
List pgsql-novice
Michael Glaesmann <grzm@myrealbox.com> writes:
> 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.)

Actually, you can use any scalar-valued expression that does not contain
a sub-SELECT or aggregate function.  (Aggregates would be meaningless
--- what are you aggregating over?  The prohibition of sub-SELECTs is
partly because we don't do planning for index expressions, which could
be fixed if anyone were sufficiently motivated, and partly because it
seems quite unlikely that the result of a SELECT could reasonably be
considered immutable.  We do *not* want to get into updating indexes for
reasons other than updates of the indexed row.)

I see your point that "functional index" is still a valid description,
but I'm inclined to bow to Peter's position that it has another meaning
that could distract people.  Presently I'm going to go with "expression
index" unless someone can provide a better choice.

            regards, tom lane

pgsql-novice by date:

Previous
From: Michael Glaesmann
Date:
Subject: Re: Functional index problems. (Was: Many joins: monthly summaries S-L--O--W)
Next
From: Marcin Gil
Date:
Subject: do large objects retain after drop database?