Re: Expressional Indexes

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> 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 ...

Note that if you're just doing this to speed up regular queries where you have
create_date in some small range, then you'll likely not see much of an
increase. Mainly you'll just save space.

What can be interesting is to create a partial index like this but over a
second unrelated column. Something like:

CREATE INDEX my_dec_03_index on my_table (userid)
WHERE (create_date >= date '2003-11-02');

Then you can do queries like

SELECT * FROM my_table WHERE userid = ? AND create_date >= date '2003-11-02'

And it'll be able to efficiently pull out just those records, even if there
are thousands more records that are older than 2003-11-02.

This avoids having to create a two-column index with a low-selectivity column
like "month".

--
greg