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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
