Skip site navigation (1) Skip section navigation (2)

Re: Indexing a boolean

From: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>
To: Kris Kiger <kris(at)musicrebellion(dot)com>
Cc: <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Indexing a boolean
Date: 2003-08-21 22:48:54
Message-ID: (view raw or whole thread)
Lists: pgsql-admin
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.

In response to


pgsql-admin by date

Next:From: Sam Barnett-CormackDate: 2003-08-21 23:20:01
Subject: Partial indexes (was: Re: Indexing a boolean)
Previous:From: Tom LaneDate: 2003-08-21 22:05:07
Subject: Re: This table won't use INDEX until I DUMP/RESTORE it ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group