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

It won't work.

You could instead have a separate boolean attribute called 'expired' for
each row.  Set this to true whenever you expire the row, and create the
partial index using that attr.

Matt

> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Jeff Boes
> Sent: 03 October 2003 17:35
> To: pgsql-admin@postgresql.org
> Subject: [ADMIN] Partial indexes ... any good theoretical discussion?
>
>
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>