Partial indexes ... any good theoretical discussion?

Поиск
Список
Период
Сортировка
От Jeff Boes
Тема Partial indexes ... any good theoretical discussion?
Дата
Msg-id d701af91955b194af069c0b945f35c16@news.teranews.com
обсуждение исходный текст
Ответы Re: Partial indexes ... any good theoretical discussion?  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Partial indexes ... any good theoretical discussion?  ("Matt Clark" <matt@ymogen.net>)
Список 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


В списке pgsql-admin по дате отправления:

Предыдущее
От: cad0022@iperbole.bologna.it
Дата:
Сообщение: How to avoid users from viewing functions code
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Partial indexes ... any good theoretical discussion?