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