Re: Index not used, performance problem

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