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