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é

[punkoff]

Vous pouvez aussi changer votre index unique en inversant les 2 colonnes.

[bibouilles]

Merci pour toutes les infos
et aussi pour la manière de créer facilement une table remplie à la volée, ce qui me sera bien utile.

Tout compte fait , il n'y a pas de contradiction avec http://use-the-index-luke.com/fr/sql...dex-concatenes
c'est juste que le plan d'exécution ne sait pas les valeurs enregistrées sur les tables, donc il ne sait pas qu'il y a un champ avec constamment les mêmes valeurs.

[aieeeuuuuu]

Bonjour,

c'est juste que le plan d'exécution ne sait pas les valeurs enregistrées sur les tables, donc il ne sait pas qu'il y a un champ avec constamment les mêmes valeurs.

si, il le sait !
Il tient des statistiques sur les colonnes indexées.
C'est d'ailleurs une des raisons pour laquelle un tel index sera sous utilisé, il n'est pas sélectif.

Cependant, quel est l’intérêt de cette colonne, si elle contient toujours la même valeur ? et surtout, quel est l’intérêt de l'indexer ?

Dans tous les cas, vous auriez - comme le dit punkoff - tout intérêt à inverser l'ordre de vos colonnes dans l'index

[Waldar]

et surtout, quel est l’intérêt de l'indexer ?

C'est une astuce qu'on utilise avec Oracle pour permettre de chercher des valeurs nulles via un index.
Par défaut elles ne sont pas indexées, mais en associant une constante le couple devient toujours non-nul et la recherche des nulls peut utiliser l'index.
http://tkyte.blogspot.fr/2006/01/som...t-nothing.html

Je n'ai pas testé chez Postgresql.

[aieeeuuuuu]

Par défaut elles ne sont pas indexées.

Merci pour la précision... et pour l'astuce

Je n'ai pas testé chez Postgresql.

bah... j'ai pas pu m’empêcher, et il semble que Postgresql index bien les null, donc inutile d'être astucieux

idem pour SQL Server

[estofilo]

L'indexation de NULL avec PostgreSQL dépend du type d'index.

Le type d'index le plus utilisé, btree, indexe les NULL
hash n'indexe pas les NULL
GIST indexe les NULL
GIN indexe les NULL à partir de la version 9.1 seulement

Il y a également une colonne amsearchnulls dans pg_am qui indique si un type d''index est utilisable pour chercher sur IS NULL/NOT NULL

En 9.3:

=> select amname,amsearchnulls from pg_am;

 amname | amsearchnulls 
--------+---------------
 btree  | t
 hash   | f
 gist   | t
 gin    | f
 spgist | t