In an attempt to throw the authorities off his trail, Jeff Boes <[EMAIL PROTECTED]> 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 "[EMAIL PROTECTED]" "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 ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
