Hi everybody.
I have some little problem on index utilisation.
Here is the schema of my base :
----------------------------------- Table "mclis" Column | Type | Modifiers
-------------+-----------------------+-----------cli_cod | numeric(9,0) | not nullcli_civ_cod | character
varying(5) | not nullcli_nomu | character varying(32) | not nullcli_pren | character varying(32) | not
nullcli_dnai | date | not nullcli_insee | character varying(13) | not nullcli_cle | character
varying(2) | not nullcli_mdp_def | character varying(6) | not null
Indexes: cli_cod_mclis_key, cli_insee_mclis_key, cli_nomu_mclis_key
Primary key: mclis_pkey
Table "mdrs" Column | Type | Modifiers
------------+----------------------+-----------dr_cod | numeric(11,0) | not nulldr_typ | character
varying(1)| not nulldr_dcre | date | not nulldr_dmaj | date | not nulldr_mnap
|numeric(18,6) | not nulldr_typdrg | character varying(1) | not nulldr_cli_cod | numeric(9,0) |dr_dpai
| date |dr_mdr_cod | character varying(3) |dr_psa_cod | numeric(6,0) |
Indexes: dr_cli_cod_mdrs_key, dr_cod_mdrs_key, dr_psa_cod_mdrs_key, idx_dr_dpai,
idx_dr_mnap
Primary key: mdrs_pkey
Table "mdecs" Column | Type | Modifiers
--------------+-----------------------+-----------dec_cod | character varying(20) | not nulldec_dcre | date
| not nulldec_typedec | character varying(1) | not nulldec_deccomp | character varying(20) |dec_dsoins
| date | not nulldec_dr_cod | numeric(11,0) | not nulldec_mntsso | numeric(12,6)
|etar_gmu_cod| character varying(6) |dec_mntmut | numeric(12,6) |dec_cli_cod | numeric(6,0) |
Indexes: dec_cod_mdecs_key, dec_dr_cod_mdecs_key
Primary key: mdecs_pkey
Table "mldecs" Column | Type | Modifiers
---------------+-----------------------+-----------ldec_dec_cod | character varying(20) | not nullldec_numlig |
charactervarying(2) | not nullldec_nexec | character varying(8) |ldec_act_clas | character varying(3) | not
nullldec_mntpaye | numeric(12,6) |ldec_pu | numeric(12,6) | not nullldec_txsso | numeric(5,2)
| not nullldec_mntsso | numeric(12,6) | not nullldec_txmut | numeric(5,2) |ldec_mntmut |
numeric(12,6) |ldec_mnttot | numeric(12,6) | not null
Indexes: idx_ldec_numlig, (on ldec_dec_cod and ldec_numlig) ldec_dec_cod_mldecs_key (on ldec_dec_cod)
Primary key: mldecs_pkey
-------------------------
When I make : (case 1)
explain select * from mldecs where ldec_dec_cod = 'x' I obtain :
NOTICE: QUERY PLAN:
Index Scan using ldec_dec_cod_mldecs_key on mldecs (cost=0.00..34449.16
rows=8792 width=155)
EXPLAIN
When i make (case 2)
explain select * from mldecs where ldec_dec_cod in ('x','y') I obtain :
NOTICE: QUERY PLAN:
Seq Scan on mldecs (cost=0.00..63928.99 rows=17540 width=155)
EXPLAIN
When i make : (case 3)
explain select * from mclis,mdrs,mdecs,mldecs
where cli_cod = 147854
and dr_cli_cod = cli_cod
and dec_dr_cod = dr_cod
and ldec_dec_cod = dec_cod;
I obtain :
NOTICE: QUERY PLAN:
Hash Join (cost=418.82..64348.03 rows=18 width=393) -> Seq Scan on mldecs (cost=0.00..55136.99 rows=1758399
width=155)-> Hash (cost=418.79..418.79 rows=12 width=238) -> Nested Loop (cost=0.00..418.79 rows=12
width=238) -> Nested Loop (cost=0.00..123.31 rows=8 width=150) -> Index Scan using
cli_cod_mclis_keyon mclis
(cost=0.00..6.01 rows=1 width=73) -> Index Scan using dr_cli_cod_mdrs_key on mdrs
(cost=0.00..116.93 rows=30 width=77) -> Index Scan using dec_dr_cod_mdecs_key on mdecs
(cost=0.00..34.97 rows=8 width=88)
EXPLAIN
I don't understanf why it makes a seq scan on table mldecs on cases 2 and 3,
according that mldecs(ldec_dec_cod) has an index (named
ldec_dec_cod_mldecs_key). The base is vacuumed and analyzed every night. How
can I use the ldec_dec_cod_mldecs_key index on mldecs ?
Regards.
Stephane.
