Discussion :

[bibouilles]

Bonjour, j'aurais une question sur l'optimisation des index

j'ai une table avec 3 champs

le premier est la clef primaire
le deuxième n'a que des 1 dedans
le troisième à des valeurs diverses

sur cette table il y a un index unique composé sur le deuxième + troisième champs

sur cette table je fait des select where champ3 =

1) comme l'index composé n'a que des 1 dans sont premier champ peut il etre utilisé ?

2) l'ajout d'un index sur le troisième champs est il intéressant?

3) Que j'ajoute ou pas le second index, un
EXPLAIN ANALYZE de ma requête ne donne pas d'"Index Scan using" ni de "Index Cond", comment cela se fait il ?

Merci d'avance

[alassanediakite]

Salut
Les index composés ne sont utilisés que si la condition de recherche contient les colonnes dans l'ordre de création de l'index.
La création d'un index sur le troisième champs est obligatoire pour votre requête (WHERE ch3=...).
Sachez par ailleurs que le SGBD n'utilise l'index que s'il le juge nécessaire! Pour le cas de PostgreSQL il faut le lui demander dans ses configurations.
Pour plus de lecture.
@+

[punkoff]

bonjour,

Les index composés ne sont utilisés que si la condition de recherche contient les colonnes dans l'ordre de création de l'index.
La création d'un index sur le troisième champs est obligatoire pour votre requête (WHERE ch3=...).

non et non


1*/ l'index pourra être utilisé avec plus ou moins d'efficacité :

si l'index est comme ceci : col2, col3 alors le sgbd sera obligé de faire un full index scan pour pouvoir n'utiliser que la 3eme colonne

si l'index est comme ceci : col3, col2 alors le SGBD pourra attaquer l'index avec un index probe (plus direct)

2*/ voir le 1

Sinon pour les explains :
Avez-vous fait un analyze de vos objets avant de regarder l'impact de l'index ?

Si la condition sur la colonne 3 n'est pas assez discriminante le SGBD peut choisir de faire un table à la place de l'utilisation de l'index, ca n'est forcément une mauvaise approche.

[alassanediakite]

non et non

En plus de l'article de sqlpro je vous propose ça.
@+

[punkoff]

http://dba.stackexchange.com/questio...esql/7484#7484


Perso pour la reproduction j'y suis pas encore arrivé.

[punkoff]

allez zou, ma table de test n'était pas assez grosse par rapport à l'index je suppose :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
drop table tt;
 
create table tt (id int, fk1 int, val1 char(100));
 
with tmp as (select generate_series(1, 1000000) as val)
insert into tt select val, val%10000, val from tmp;
 
 
create index idx_tt on tt (id, fk1);
 
analyze;
 
 
explain analyze
select *
from tt where fk1 = 100;

explain :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
"Index Scan using idx_tt on tt  (cost=0.42..18655.72 rows=98 width=109) (actual time=0.022..22.241 rows=100 loops=1)"
"  Index Cond: (fk1 = 100)"
"Total runtime: 22.265 ms"

En créant un index "propre" sur fk1 on peut voir le temps d’exécution bien inférieur, avec un plan bien différent :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
create index idx_tt2 on tt (fk1);
 
analyze;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
"Bitmap Heap Scan on tt  (cost=5.18..376.24 rows=98 width=109) (actual time=0.041..0.111 rows=100 loops=1)"
"  Recheck Cond: (fk1 = 100)"
"  ->  Bitmap Index Scan on idx_tt2  (cost=0.00..5.16 rows=98 width=0) (actual time=0.027..0.027 rows=100 loops=1)"
"        Index Cond: (fk1 = 100)"
"Total runtime: 0.128 ms"

Si on force un table scan, on pourra voir que le temps d'exec est encore plus long quand dans le cas 1 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
explain analyze
select *
from tt
where fk1 = 100 or val1 = '100';

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
"Seq Scan on tt  (cost=0.00..32242.00 rows=99 width=109) (actual time=0.044..204.252 rows=100 loops=1)"
"  Filter: ((fk1 = 100) OR (val1 = '100'::bpchar))"
"  Rows Removed by Filter: 999900"
"Total runtime: 204.278 ms"

[bibouilles]

Bonjour merci beaucoup de votre participation,

mon cas n'était pas tout à fait celui-là mais un peu tordu et difficile à s'imaginer de fait.
En fait dans le premier champs de l'index je n'ai qu'une valeur, toujours la même, d'où la confusion avec unique

A la suite des derniers scripts, j'ai donc fait

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
 
DROP TABLE tt;
 
CREATE TABLE tt (id int, fk1 int, val1 char(100));
 
WITH tmp AS (SELECT generate_series(1, 1000000) AS val)
INSERT INTO tt SELECT val, 1, val FROM tmp;
 
analyze;
 
EXPLAIN analyze
SELECT *
FROM tt WHERE val1 = '100';
 
"Seq Scan on tt  (cost=0.00..29742.00 rows=1 width=109) (actual time=0.144..340.945 rows=1 loops=1)"
"  Filter: (val1 = '100'::bpchar)"
"Total runtime: 341.004 ms"
 
CREATE INDEX idx_tt ON tt (fk1, val1);
 
analyze;
 
EXPLAIN analyze
SELECT *
FROM tt WHERE val1 = '100';
 
"Seq Scan on tt  (cost=0.00..29742.00 rows=1 width=109) (actual time=0.170..260.504 rows=1 loops=1)"
"  Filter: (val1 = '100'::bpchar)"
"Total runtime: 260.575 ms"
 
 
DROP INDEX idx_tt;
 
CREATE INDEX idx_tt ON tt (val1);
 
analyze;
 
EXPLAIN analyze
SELECT *
FROM tt WHERE val1 = '100';
 
"Index Scan using idx_tt on tt  (cost=0.00..8.92 rows=1 width=109) (actual time=0.460..0.463 rows=1 loops=1)"
"  Index Cond: (val1 = '100'::bpchar)"
"Total runtime: 0.517 ms"

Donc du coup, seul l'index non composé peut être utilisé pour mon cas.
Cela semble donc en contradiction avec http://use-the-index-luke.com/fr/sql...dex-concatenes
voir Figure 2.1. Index concaténé