Tom Lane <tgl@sss.pgh.pa.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
