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

Re: Index not used, performance problem

From: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>
To: Andreas Kostyrka <andreas(at)mtg(dot)co(dot)at>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Index not used, performance problem
Date: 2003-03-31 18:21:45
Message-ID: Pine.LNX.4.33.0303311118440.12130-100000@css120.ihs.com (view raw or flat)
Thread:
Lists: pgsql-performance
On 29 Mar 2003, Andreas Kostyrka wrote:

> On Sat, 2003-03-29 at 14:47, Christopher Kings-Lynne wrote:
> > Hi Andreas,
> > 
> > A few points:
> > 
> > PostgreSQL is rarely going to use an index for a boolean column.  The
> > reason is that since almost by definition true will occupy 50% of the rows
> > and false will occupy 50% (say).  In this case, a sequential scan is
> > always faster.  You would say that the 'selectivity' isn't good enough.
> Well, perhaps it should collect statistics, because a "deleted" column
> is a prime candidate for a strongly skewed population.

It does.  When you run analyze.  You have vacuumed and analyzed the 
database right?

Assuming you have, it's often better to make a partial index for your 
booleans.  I'll assume that patient.deleted being true is a more rare 
condition than false, since false is the default.

So, create your index this way to make it smaller and faster:

create index dxname on sometable (bool_field) where bool_field IS TRUE;

Now you have a tiny little index that gets scanned ultra fast and is easy 
to maintain.  You have to, however, access it the same way.  the proper 
way to reference a bool field is with IS [NOT] {TRUE|FALSE}

select * from some_table where bool_field IS TRUE would match the index I 
created aboce.

select * from some_table where bool_field = 't' would not.


In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2003-03-31 18:53:54
Subject: Re: Index not used, performance problem
Previous:From: Tom LaneDate: 2003-03-31 17:54:46
Subject: Re: WAL monitoring and optimizing

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