Re: partial indexed not being used.

Michael Fuhr schreef:
> 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?
>

Michael,

most of the time means: 9 out of ten times.

> select version();

# select version();
                                                             version

-------------------------------------------------------------------------------------------------------------------------------
  PostgreSQL 7.4.7 on i386-redhat-linux-gnu, compiled by GCC i386-redhat-linux-gcc (GCC) 3.3.3 20040412 (Red Hat Linux
3.3.3-7)
(1 row)

> select count(*) from cdr;

# select count(*) from cdr;
  count
-------
  62547
(1 row)

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

# select count(*) from cdr where billed = false;
  count
-------
      3
(1 row)


I am worried that is is doing a sequential scan. On production systems
the cdr table might contain of millions of records.

Thanks,
Ron

>> =# 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.
>