Partial indexes (was: Re: Indexing a boolean)

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