Planner doesn't chose Index - (slow select) - Mailing list pgsql-performance

Hi all,

I've been struggling with some performance issues with certain
SQL queries.  I was prepping a long-ish overview of my problem
to submit, but I think I'll start out with a simple case of the
problem first, hopefully answers I receive will help me solve
my initial issue.

Consider the following two queries which yield drastically different
run-time:

db=# select count(*) from pk_c2 b0 where b0.offer_id=7141;
 count
-------
     1
(1 row)
Time: 5139.004 ms

db=# select count(*) from pk_c2 b0 where b0.pending=true and b0.offer_id=7141;
 count
-------
     1
(1 row)
Time: 1.828 ms


That's 2811 times faster!

Just to give you an idea of size of pk_c2 table:

db=# select count(*) from pk_c2 ;
  count
---------
 2158094
(1 row)
Time: 5275.782 ms

db=# select count(*) from pk_c2 where pending=true;
 count
-------
    51
(1 row)
Time: 5073.699 ms



db=# explain select count(*) from pk_c2 b0 where b0.offer_id=7141;
QUERY PLAN
---------------------------------------------------------------------------
 Aggregate  (cost=44992.78..44992.78 rows=1 width=0)
   ->  Seq Scan on pk_c2 b0  (cost=0.00..44962.50 rows=12109 width=0)
         Filter: (offer_id = 7141)
(3 rows)
Time: 1.350 ms

db=# explain select count(*) from pk_c2 b0 where b0.pending=true and
b0.offer_id=7141;
QUERY PLAN
----------------------------------------------------------------------------------------
 Aggregate  (cost=45973.10..45973.10 rows=1 width=0)
   ->  Index Scan using pk_boidx on pk_c2 b0  (cost=0.00..45973.09
rows=1 width=0)
         Index Cond: (offer_id = 7141)
         Filter: (pending = true)
(4 rows)
Time: 1.784 ms



The table has indexes for both 'offer_id' and '(pending=true)':

Indexes:
    "pk_boidx" btree (offer_id)
    "pk_bpidx" btree (((pending = true)))

So, why would the planner chose to use the index on the second query
and not on the first?


Note that I am able to fool the planner into using an Index scan
on offer_id by adding a silly new condition in the where clause of
the first form of the query:


db=# explain select count(*) from pk_c2 b0 where b0.offer_id=7141 and oid > 1;
QUERY PLAN
-------------------------------------------------------------------------------------------
 Aggregate  (cost=45983.19..45983.19 rows=1 width=0)
   ->  Index Scan using pk_boidx on pk_c2 b0  (cost=0.00..45973.09
rows=4037 width=0)
         Index Cond: (offer_id = 7141)
         Filter: (oid > 1::oid)
(4 rows)
Time: 27.301 ms

db=# select count(*) from pk_c2 b0 where b0.offer_id=7141 and oid > 1;
 count
-------
     1
(1 row)
Time: 1.900 ms

What gives?

This seems just too hokey for my taste.

--patrick



db=# select version();
                                 version
-------------------------------------------------------------------------
 PostgreSQL 7.4.12 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.6