pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp on indexes
2+ messages / 2 participants
[nested] [flat]
* Help on indexes
@ 2002-05-15 12:08 Stephane DEWITTE <stephane@smeso.fr>
2002-05-15 15:19 ` Re: Help on indexes Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 2+ messages in thread
From: Stephane DEWITTE @ 2002-05-15 12:08 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Help on indexes
2002-05-15 12:08 Help on indexes Stephane DEWITTE <stephane@smeso.fr>
@ 2002-05-15 15:19 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 2+ messages in thread
From: Josh Berkus @ 2002-05-15 15:19 UTC (permalink / raw)
To: Stephane DEWITTE <stephane@smeso.fr>; pgsql-sql
Stephane,
> 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 ?
First question: Have you actually measured the execution time of the
various queries? There are many cases where a Seq Scan is faster than
an Index Scan. For example, if in Case2, 50% of the entries were
values 'X' of 'Y', then a Seq Scan is the better approach.
Please determine that you actually have a problem with slow execution.
Otherwise, the query parser is just doing its job.
-Josh Berkus
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-05-15 15:19 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-05-15 12:08 Help on indexes Stephane DEWITTE <stephane@smeso.fr>
2002-05-15 15:19 ` Josh Berkus <josh@agliodbs.com>
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