Re: partial indexed not being used.

On Mon, Feb 12, 2007 at 01:23:13AM +0100, Ron Arts wrote:
> I have a table that has a boolean column telling if that particular
> record has been billed already. So most of the time that column holds
> only true values.

How often is "most of the time"? What are the results of the following
queries?

select version();
select count(*) from cdr;
select count(*) from cdr where billed = false;

> =# explain select cdr.* from cdr where billed = false order by calldate;
>
> QUERY PLAN
> -------------------------------------------------------------------
> Sort (cost=37448.75..37526.94 rows=31273 width=465)
> Sort Key: calldate
> -> Seq Scan on cdr (cost=0.00..20323.81 rows=31273 width=465)
> Filter: (billed = false)
>
> How can I ensure the patial billing index will be used?

If using the index would be slower than a sequential scan then you
don't want the query to use the index. Let's see how accurate the
row count estimates are and whether using an index really would be
faster -- please post the output of the following queries:

set enable_seqscan to on;
explain analyze select cdr.* from cdr where billed = false order by calldate;
set enable_seqscan to off;
explain analyze select cdr.* from cdr where billed = false order by calldate;

If the number of rows returned isn't close to the planner's estimate
(31273 in the output you showed) then trying running ANALYZE or
VACUUM ANALYZE on the table, then run the above statements again.

--
Michael Fuhr