pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Matt Mello <alien@spaceship.com>
To: pgsql-performance@postgresql.org
Subject: Re: Index not used, performance problem
Date: Sat, 29 Mar 2003 11:55:00 -0600
Message-ID: <3E85DDF4.7060406@spaceship.com> (raw)
In-Reply-To: <1048957151.7543.19.camel@andi-lap>
References: <20030329214139.N4697-100000@houston.familyhealth.com.au>
	<1048957151.7543.19.camel@andi-lap>

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




view thread (11+ messages)  latest in thread

Message-ID: <3E85DDF4.7060406@spaceship.com>
Permalink:  ../3E85DDF4.7060406@spaceship.com/
Also on:    postgresql.org/message-id/3E85DDF4.7060406@spaceship.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: alien@spaceship.com
  Subject: Re: Index not used, performance problem
  In-Reply-To: <3E85DDF4.7060406@spaceship.com>
  References: <20030329214139.N4697-100000@houston.familyhealth.com.au> <1048957151.7543.19.camel@andi-lap> <3E85DDF4.7060406@spaceship.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox