Partial indexes (was: Re: Indexing a boolean)

Поиск
Список
Период
Сортировка
От Sam Barnett-Cormack
Тема Partial indexes (was: Re: Indexing a boolean)
Дата
Msg-id Pine.LNX.4.50.0308220018410.25250-100000@short.lancs.ac.uk
обсуждение исходный текст
Ответ на Re: Indexing a boolean  ("scott.marlowe" <scott.marlowe@ihs.com>)
Ответы Re: Partial indexes (was: Re: Indexing a boolean)  ("scott.marlowe" <scott.marlowe@ihs.com>)
Список pgsql-admin
On Thu, 21 Aug 2003, scott.marlowe wrote:

> On Thu, 21 Aug 2003, Kris Kiger wrote:
>
> > I would appreciate it if I could get some thoughts on indexing a field
> > with only two values?  For example, I have a table with a few million
> > rows in it.  All items in this table are broken up into two categories
> > using 'T' or 'F'.  It seems logical to me that an index on this field
> > would create two logical 'buckets', so that one could say, "I want all
> > 'T' values", or "I want all 'F' values" and merely have to look in the
> > appropriate bucket, rather than have to perform a sequential scan
> > through three million items every time a request is made based on 'T' or
> > 'F'.  If I were to create an index on a char(1) field that contains only
> > values of 'T' or 'F', would the query analyzer override the use of this
> > index?  How does Postgres address this problem and what are all of your
> > thoughts on this issue?  I appreciate the help!
>
> Often the best approach here is to make a partial index:
>
> create index table_dx on table (bool_field) where bool_field IS TRUE;
>
> This works well if you have a large portion of the boolean fields set to
> FALSE, and want to find the few that are TRUE.  Reverse the TRUE and false
> for other situations.

A more general question:

Can the planner tell which index to use if there is more than one that
fits the bill? Like if there is a full index and one or more partial
indexes on a field, can it determine which to use for a given query?

--

Sam Barnett-Cormack
Software Developer                           |  Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk)  |  Lancaster University

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

Предыдущее
От: "scott.marlowe"
Дата:
Сообщение: Re: Indexing a boolean
Следующее
От: "scott.marlowe"
Дата:
Сообщение: Re: Partial indexes (was: Re: Indexing a boolean)