Partial indexes ... any good theoretical discussion?

From: Jeff Boes <jboes(at)nexcerpt(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Partial indexes ... any good theoretical discussion?
Date: 2003-10-03 16:34:48
Message-ID: d701af91955b194af069c0b945f35c16@news.teranews.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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)?

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

--
Jeff Boes vox 269.226.9550 ext 24
Database Engineer fax 269.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2003-10-03 17:17:17 Re: Partial indexes ... any good theoretical discussion?
Previous Message Tom Lane 2003-10-03 13:27:10 Re: pg_restore fails - postgres 7.3.4