par Guillaume Lelarge.

Distinguer les prédicats d'accès et de filtre


La base de données PostgreSQL utilise trois méthodes différentes pour appliquer les clauses where (prédicats) :

Prédicat d'accès (« Index Cond »)

Les prédicats d'accès expriment les conditions de début et d'arrêt du parcours de nœuds feuilles.

Prédicat de filtre d'index (« Index Cond »)

Les prédicats de filtre d'index sont appliqués seulement lors du parcours des nœuds feuilles. Ils ne contribuent pas aux conditions de début et de fin, et ne diminuent pas l'intervalle parcouru.

Prédicat de filtre au niveau table (« Filter »)

Les prédicats sur les colonnes qui ne font pas partie de l'index sont évalués sur le niveau de la table. Pour cela, la base de données doit charger la ligne de la table.

Remarque

Les prédicats de filtre d'index donne une fausse impression de sécurité. Même si un index est utilisé, les performances dégradent rapidement sur un volume de données grossissant ou sur une charge système en augmentation.

Les plans d'exécution de PostgreSQL ne montrent pas les prédicats d'accès et de filtre d'index séparément. Ils s'affichent tous les deux sous le titre « Index Cond ». Cela signifie que le plan d'exécution doit être comparé à la définition de l'index pour différencier les prédicats d'accès des prédicats de filtre d'index.

Remarque

Le plan d'exécution PostgreSQL ne fournit pas suffisamment d'informations pour trouver les prédicats de filtre d'index.

Les prédicats affichés comme « Filter » sont toujours des prédicats de filtre au niveau table, même lorsqu'ils sont affichés au niveau de l'opération Index Scan.

Considérez l'exemple suivant, cité la première fois dans le chapitre « Performances et scalabilité » (script create & insert):

CREATE TABLE donnees (
   section NUMERIC NOT NULL,
   id1     NUMERIC NOT NULL,
   id2     NUMERIC NOT NULL
);
CREATE INDEX donnees_key ON donnees(section, id1);

Le select suivant filtre sur la colonne ID2 qui n'est pas inclus dans l'index :

PREPARE req(int) AS SELECT count(*) 
                       FROM donnees
                      WHERE section = 1
                        AND id2 = $1;
EXPLAIN EXECUTE req(1);

                      QUERY PLAN
-----------------------------------------------------
Aggregate  (cost=529346.31..529346.32 rows=1 width=0)
  Output: count(*)
  -> Index Scan using donnees_key on donnees
     (cost=0.00..529338.83 rows=2989 width=0)
     Index Cond: (donnees.section = 1::numeric)
     Filter: (donnees.id2 = ($1)::numeric)

Le prédicat ID2 est affiché en tant que « Filter » sous l'opération Index Scan. Ceci est dû au fait que PostgreSQL réalise un accès à la table lors de l'opération Index Scan. En d'autres termes, l'opération TABLE ACCESS BY INDEX ROWID de la base de données Oracle est cachée dans l'opération Index Scan de PostgreSQL. De ce fait, il est possible qu'un Index Scan filtre sur des colonnes qui ne sont pas incluses dans l'index.

Important

Les prédicats Filter de PostgreSQL sont des prédicats de filtre de niveau table, même quand ils sont affichés en tant qu'Index Scan.

Quand nous ajoutons l'index à partir du chapitre « Performances et scalabilité », nous pouvons voir que toutes les colonnes s'affichent en tant que « Index Cond », que ce soit des prédicats d'accès ou de filtre.

CREATE INDEX scale_lent 
          ON donnees (section, id1, id2);

Le plan d'exécution avec le nouvel index ne montre aucune condition de filtre :

                      QUERY PLAN
------------------------------------------------------
Aggregate  (cost=14215.98..14215.99 rows=1 width=0)
  Output: count(*)
  -> Index Scan using scale_lent on donnees 
     (cost=0.00..14208.51 rows=2989 width=0)
     Index Cond: (section = 1::numeric AND id2 = ($1)::numeric)

Veuillez noter que la condition sur ID2 ne peut pas diminuer le parcours de nœuds feuilles car l'index a la colonne ID1 avant la colonne ID2. L'Index Scan va parcourir l'intervalle entier pour trouver la condition SECTION=1::numeric et appliquer le filtre ID2=($1)::numeric sur chaque ligne qui remplit la clause sur SECTION.

Astuce

À propos de l'auteur

Photo de Markus Winand

Markus Winand teaches efficient SQL—inhouse and online. He minimizes the development time using modern SQL and optimizes the runtime with smart indexing—for that he also published the book SQL Performance Explained.

Livre de Markus

Couverture du livre « SQL : Au cœur des performances »

L'essence de SQL tuning dans 200 pages.

Acheter de Markus
(Livre de poche et PDF)

Achetez chez Amazon
(Seulement en poche)

“Use The Index, Luke!” by Markus Winand and translated by Guillaume Lelarge is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Mentions légales | Contact | NO WARRANTY | Marque déposée | Privacy | CC-BY-NC-ND 3.0 license