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

Re: Index not used, performance problem

From: Matt Mello <alien(at)spaceship(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Index not used, performance problem
Date: 2003-03-29 17:55:00
Message-ID: 3E85DDF4.7060406@spaceship.com (view raw or flat)
Thread:
Lists: pgsql-performance
I have many boolean columns, and my queries almost always use indexes. 
Just because a column can have only 2 values does not mean that 50% of 
them will be true and 50% will be false.  The ratio of T|F depends on 
the content.  I have some boolean columns with less than 1% true. 
Obviously, an index will help with these ... and it does, tremendously.

If you only have 6400 rows, it is *possible* that the planner will 
choose not to use an index, as using an index might be slower than just 
seqscanning.

If you do lots of updates on that table, you might need to do a vacuum 
full occasionally, although I'm not certain how much that benefits a 
boolean field.

Also, if possible, I would consider upgrading to a more recent version. 
  I have seen many of the experts here post news about significant bug 
fixes between 7.2 and 7.3.  (My experience with boolean fields is using 
7.3.)

In addition, when posting to the list, it is helpful to post an "explain 
analyze" for a query, as it gives more & better details (for those same 
experts, of which I am not).


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.
> 
> Andreas

-- 
Matt Mello
512-350-6900


In response to

pgsql-performance by date

Next:From: Wil PetersDate: 2003-03-29 21:17:26
Subject: Bad perfomance of pl/pgsql-function on new server
Previous:From: Jord TannerDate: 2003-03-29 16:59:29
Subject: Re: Index not used, performance problem

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