Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 959024759AA for ; Wed, 15 May 2002 08:08:05 -0400 (EDT) Received: from apache.smeso.fr (unknown [212.234.3.50]) by postgresql.org (Postfix) with ESMTP id 6B5D347589D for ; Wed, 15 May 2002 08:08:03 -0400 (EDT) Received: from STEPHFIXE (ocp.smeso.fr [192.168.168.35]) by apache.smeso.fr (8.9.3/8.9.3) with SMTP id OAA07888 for ; Wed, 15 May 2002 14:08:05 +0200 From: =?us-ascii?Q?Stephane_DEWITTE?= To: Subject: Help on indexes Date: Wed, 15 May 2002 14:08:37 +0200 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Importance: Normal In-Reply-To: X-Archive-Number: 200205/156 X-Sequence-Number: 7792 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.