From: | Stephane DEWITTE <stephane(at)smeso(dot)fr> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Help on indexes |
Date: | 2002-05-15 12:08:37 |
Message-ID: | NOEAIPGJHJIHECKNEIBGIECHCPAA.stephane@smeso.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 null
cli_civ_cod | character varying(5) | not null
cli_nomu | character varying(32) | not null
cli_pren | character varying(32) | not null
cli_dnai | date | not null
cli_insee | character varying(13) | not null
cli_cle | character varying(2) | not null
cli_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 null
dr_typ | character varying(1) | not null
dr_dcre | date | not null
dr_dmaj | date | not null
dr_mnap | numeric(18,6) | not null
dr_typdrg | character varying(1) | not null
dr_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 null
dec_dcre | date | not null
dec_typedec | character varying(1) | not null
dec_deccomp | character varying(20) |
dec_dsoins | date | not null
dec_dr_cod | numeric(11,0) | not null
dec_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 null
ldec_numlig | character varying(2) | not null
ldec_nexec | character varying(8) |
ldec_act_clas | character varying(3) | not null
ldec_mntpaye | numeric(12,6) |
ldec_pu | numeric(12,6) | not null
ldec_txsso | numeric(5,2) | not null
ldec_mntsso | numeric(12,6) | not null
ldec_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_key on 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Roberto Mello | 2002-05-15 14:49:29 | Re: some questions |
Previous Message | Marc SCHAEFER | 2002-05-15 10:00:17 | Re: Various PostgreSQL questions |