Forums PostgreSQL.fr

bidouilles

Membre

postgresql Utilisation des index composés et EXPLAIN

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

arthurr

Membre

Re : postgresql Utilisation des index composés et EXPLAIN

Bonjour,
Pour que PostgreSQL utilise votre index sans avoir à en re-créer un, il suffit de faire un where champ3 = ?? and champ2 = 1

gleu

Administrateur

Re : postgresql Utilisation des index composés et EXPLAIN

1. Non.
2. Oui.
3. Cela dépend de la requête. Pouvez-vous fournir la requête et le résultat du EXPLAIN ANALYZE ?

---

Guillaume.

bidouilles

Membre

Re : postgresql Utilisation des index composés et EXPLAIN

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"

à priori 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é

gleu

Administrateur

Re : postgresql Utilisation des index composés et EXPLAIN

à priori seul l'index non composé peut être utilisé pour mon cas

Oui, en effet.

cela semble donc en contradiction avec http://use-the-index-luke.com/fr/sql...dex-concatenes
voir Figure 2.1. Index concaténé

Non, absolument pas. La figure 2.1 montre qu'il n'est pas possible de ne rechercher que par le deuxième champ car les données ne sont pas triées globalement pour lui. C'est d'ailleurs bien expliqué dans le texte avant la figure 2.1 ("Cela signifie qu’un index à deux colonnes ne permet pas une recherche sur la deuxième colonne seule.") et après ("Du coup, l’arbre est inutile pour cette requête."). Le fait que vous avez toujours la même valeur pour la première colonne est quelque chose que vous savez mais pas le planificateur. Il faudrait qu'il parcourt l'index entier pour s'en rendre compte.

---

Guillaume.

arthurr

Membre

Re : postgresql Utilisation des index composés et EXPLAIN

En gardant votre UNIQUE index (fk1,val1) et en ajoutant une clause WHERE sur fk1 = 1 vous avez un fonctionnement assez proche :
Index sur val1 :

explain analyse SELECT * FROM tt WHERE val1 = '100' ;
                                                  QUERY PLAN                                                  
--------------------------------------------------------------------------------------------------------------
 Index Scan using idx_tt2 on tt  (cost=0.55..4.57 rows=1 width=109) (actual time=0.028..0.028 rows=1 loops=1)
   Index Cond: (val1 = '100'::bpchar)
 Planning time: 0.074 ms
 Total runtime: 0.050 ms

Index unique sur fk1 + val1 :

explain analyse SELECT * FROM tt WHERE val1 = '100' and fk1=1;
                                                 QUERY PLAN                                                  
-------------------------------------------------------------------------------------------------------------
 Index Scan using idx_tt on tt  (cost=0.55..4.57 rows=1 width=109) (actual time=0.033..0.034 rows=1 loops=1)
   Index Cond: ((fk1 = 1) AND (val1 = '100'::bpchar))
 Planning time: 0.098 ms
 Total runtime: 0.058 ms
(4 rows)

Je ne dis pas que c'est ce qui est le plus propre, mais ça répond à la première question (1) comme l'index composé n'a que des 1 dans sont premier champ peut il etre utilisé ?)

bidouilles

Membre

Re : postgresql Utilisation des index composés et EXPLAIN

merci bien gleu et arthurr c'est exactement les réponses que je cherchais