Help on indexes - Mailing list pgsql-sql

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.