Re: partial indexed not being used.

From: Ron Arts <ron(dot)arts(at)neonova(dot)nl>
To: Michael Fuhr <mike(at)fuhr(dot)org>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: partial indexed not being used.
Date: 2007-02-12 08:08:10
Message-ID: 45D0206A.1090400@neonova.nl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Gustavo 2007-02-12 10:58:41 Help whit schemas
Previous Message Tom Lane 2007-02-12 06:33:07 Re: GiST Comparing IndexTuples/Datums