Re: Expressional Indexes

"Randolf Richardson, DevNet SysOp 29" <rr(at)8x(dot)ca> writes:
> For the application I will need to develop in the future, it would be
> okay for this index to hold data more than 30 days old, and then be
> redefined on a monthly basis along with regular database vacuuming (and
> other maintenance).

Okay ...

> Could this be done with a "partial index" as follows?
> CREATE INDEX my_index on my_table (create_date)
> WHERE (create_date > age(timestamp '30 days'));

No, because the above represents a moving cutoff; it will (and should)
be rejected as a non-immutable predicate condition. You could do
something like

CREATE INDEX my_Nov_03_index on my_table (create_date)
WHERE (create_date >= date '2003-11-01');

and then a month from now replace this with

CREATE INDEX my_Dec_03_index on my_table (create_date)
WHERE (create_date >= date '2003-12-01');

bearing in mind that this index can be used with queries that contain
WHERE conditions like "create_date >= some-date-constant". The planner
must be able to convince itself that the right-hand side of the WHERE
condition is >= the cutoff in the index's predicate condition. Since
the planner is not very bright, both items had better be simple DATE
constants, or it won't be able to figure it out ...

> So an "Expression Index" could, for example, be used to sort alpha-
> numeric data in a case-insensitive manner? I just want to make sure I'm
> understanding this correctly.

It won't do anything you could not have done in prior releases using a
custom-built function as the named function of a functional index. In
that sense it's just a notational improvement rather than a real advance
in capability.

(Actually I guess there is an advance in capability: 7.4 lets you build
a multi-column index on multiple expressions, whereas in prior releases
a functional index could only have one index column. Not sure how
important this is in practice though.)

regards, tom lane