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

In an attempt to throw the authorities off his trail, Jeff Boes <jboes(at)nexcerpt(dot)com> transmitted:
> I've only just now noticed that CREATE INDEX accepts a 'WHERE'
> clause. This is used to create something called a "partial index".
> Hmm, ever being one who sees the world as made of nails when first
> given a hammer ...
>
> One of our tables, with a couple hundred thousand rows) has a
> date-column index. We expire things out of the table every day (the
> vast majority, but not exclusively, stuff that's a couple days
> old). We're frequently running queries against the table, looking
> for "everything since this time yesterday"; we hardly ever look back
> more than 24 hours.
>
> 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? Do index values older than 24
> hours drop out? Or must I "refresh" the index from time to time (in
> our application, probably a couple dozen times a day)?

That won't work, unfortunately.

[somedatabase]=# create index partial on transaction_log(trans_on) where
trans_on > now() - '5 days'::interval;
ERROR: functions in index predicate must be marked IMMUTABLE
[somedatabase]=#

You can't have now() (which is certainly *not* immutable) as part of
the index.

A better idea would be to have a set of several tables, one for each
day, UNION ALL them together to generate a view to support queries,
and use a sequence to control which table is inserted to on any given
day, alongside some rules for insert/deletes.
--
(format nil "~S(at)~S" "aa454" "freenet.carleton.ca")
http://cbbrowne.com/info/linuxxian.html
"Waving away a cloud of smoke, I look up, and am blinded by a bright,
white light. It's God. No, not Richard Stallman, or Linus Torvalds,
but God. In a booming voice, He says: "THIS IS A SIGN. USE LINUX, THE
FREE Unix SYSTEM FOR THE 386." -- Matt Welsh