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: Pine.LNX.4.33.0308211644320.15011-100000@css120.ihs.com (view raw or flat)
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

Responses

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-2014 The PostgreSQL Global Development Group