PG using index+filter instead only use index - Mailing list pgsql-performance

Hi,

PostgreSQL 8.4.2 / default_statistics_target = 300

I have a strange problem for a bad choose of indexes.

client=# \d ct13t
         Table "public.ct13t"
   Column   |     Type     | Modifiers
------------+--------------+-----------
 ct12emp04  | integer      | not null
 ct03emp01  | integer      | not null
 ct03tradut | integer      | not null
 ct07emp01  | integer      | not null
 ct07c_cust | integer      | not null
 ct13dtlanc | date         | not null
 ct12numlot | integer      | not null
 ct12numlan | integer      | not null
 ct13emptr1 | integer      |
 ct13tradu1 | integer      |
 ct13empcc1 | integer      |
 ct13ccust1 | integer      |
 ct13duoc   | character(1) |
Indexes:
    "ct13t_pkey" PRIMARY KEY, btree (ct12emp04, ct03emp01, ct03tradut,
ct07emp01, ct07c_cust, ct13dtlanc, ct12numlot, ct12numlan) CLUSTER
    "ict13t1" btree (ct12emp04, ct12numlot, ct12numlan)
    "ict13t2" btree (ct07emp01, ct07c_cust)
    "ict13t3" btree (ct13empcc1, ct13ccust1)
    "ict13t4" btree (ct03emp01, ct03tradut)
    "ict13t5" btree (ct13emptr1, ct13tradu1)
    "uct13t" btree (ct12emp04, ct13dtlanc)


client=# explain analyze SELECT ct12emp04, ct03emp01, ct03tradut,
ct07emp01, ct07c_cust, ct13dtlanc, ct12numlot, ct12numlan FROM CT13T
WHERE ct12emp04 = '2' AND ct03emp01 = '2' AND ct03tradut = '60008' AND
ct07emp01 = '2' AND ct07c_cust = '0' AND ct13dtlanc =
'2005-01-28'::date AND ct12numlot = '82050128' AND ct12numlan = '123';

     QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using ict13t2 on ct13t  (cost=0.00..5.69 rows=1 width=32)
(actual time=288.687..288.687 rows=0 loops=1)
   Index Cond: ((ct07emp01 = 2) AND (ct07c_cust = 0))
   Filter: ((ct12emp04 = 2) AND (ct03emp01 = 2) AND (ct03tradut =
60008) AND (ct13dtlanc = '2005-01-28'::date) AND (ct12numlot =
82050128) AND (ct12numlan = 123))
 Total runtime: 288.735 ms
(4 rows)

client=# create table ad_ct13t as select * from ct13t;
SELECT
client=# alter table ad_ct13t add primary key (ct12emp04, ct03emp01,
ct03tradut, ct07emp01, ct07c_cust, ct13dtlanc, ct12numlot,
ct12numlan);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
"ad_ct13t_pkey" for table "ad_ct13t"
ALTER TABLE
client=# explain analyze SELECT ct12emp04, ct03emp01, ct03tradut,
ct07emp01, ct07c_cust, ct13dtlanc, ct12numlot, ct12numlan FROM
AD_CT13T WHERE ct12emp04 = '2' AND ct03emp01 = '2' AND ct03tradut =
'60008' AND ct07emp01 = '2' AND ct07c_cust = '0' AND ct13dtlanc =
'2005-01-28'::date AND ct12numlot = '82050128' AND ct12numlan = '123';

                            QUERY PLAN


---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-
 Index Scan using ad_ct13t_pkey on ad_ct13t  (cost=0.00..5.66 rows=1
width=32) (actual time=0.090..0.090 rows=0 loops=1)
   Index Cond: ((ct12emp04 = 2) AND (ct03emp01 = 2) AND (ct03tradut =
60008) AND (ct07emp01 = 2) AND (ct07c_cust = 0) AND (ct13dtlanc =
'2005-01-28'::date) AND (ct12numlot = 82050128) AND (ct12numlan =
123))
 Total runtime: 0.146 ms
(3 rows)

My question: if the cost is exactly the same, why PG choose the index
ict13t2 on ct13t and apply a filter instead use the primary key ?
In one query, it's ok. But this routine execute millions times this query.

Thanks for any help,

Alexandre