Re: Partial indexes ... any good theoretical discussion?

Jeff Boes <jboes(at)nexcerpt(dot)com> writes:
> If I created the index as something like:
> CREATE INDEX ix_foo ON foo(the_date)
> WHERE the_date >= now() - interval '24 hours';
> what might I expect as the impact?

You won't be allowed to, because now() isn't immutable, and only
immutable functions are allowed in index expressions and predicates.

You could imagine a batch job every night creating a new index

CREATE INDEX ix_foo_20031003 ON foo(the_date)
WHERE the_date >= '2003-10-03'

and then dropping the prior index. Dunno if this would be worth the trouble,
but it might be. The CREATE INDEX should run quite quickly if it only
has to pick up a few rows, which it would if you run it shortly after
the chosen boundary.

> And, absent pat answers to this, is there anything out there in PG-land
> that documents partial indexes, and when to use them?

http://developer.postgresql.org/docs/postgres/indexes-partial.html
The links at the bottom of the page point to various academic
papers (I hope the links all still work...)

regards, tom lane