From: Stephane DEWITTE <stephane@smeso.fr>
To: pgsql-sql@postgresql.org
Subject: Help on indexes
Date: Wed, 15 May 2002 14:08:37 +0200
Message-ID: <NOEAIPGJHJIHECKNEIBGIECHCPAA.stephane@smeso.fr> (raw)
In-Reply-To: <GNELIHDDFBOCMGBFGEFOMEJOCCAA.chriskl@familyhealth.com.au>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: stephane@smeso.fr
Subject: Re: Help on indexes
In-Reply-To: <NOEAIPGJHJIHECKNEIBGIECHCPAA.stephane@smeso.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox