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?
Re: Partial indexes ... any good theoretical discussion? |
Список | 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 по дате отправления: