Re: Partial indexes (was: Re: Indexing a boolean)

On Fri, 22 Aug 2003, Sam Barnett-Cormack wrote:

> 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?

Basically, for partial / functional indexes, the create index where clause
needs to exactly (or nearly so) match the select query's where clause:

create index table_dx on table (bool_field) where bool_field IS TRUE;
select * from table where bool_field = 't'; <- might not match (currently
won't match)
select * from table where bool_field IS TRUE; <- will match

And yes, the query planner will usually know to use the most selective
index, i.e. the smaller, cheaper to use index.