vendredi 18 août 2017

Postgres et performances de l'indexage

Je suis en train d'effectuer un changement de types sur des grosses tables partitionnées (jusqu'à 1 TB par groupe de tables), et je découvre quelques aspects intéressants des performances de ce genre d'opérations. Aujourd'hui, parlons des performances de l'indexage.

Pour illustrer, voyons ce script qui créé une table avec 3 colonnes d'entiers, bytea et texte. Ajoutons 10.000.000 de lignes, et indexons chaque colonne à son tour.

create table t(f1 int not null, f2 bytea not null, f3 text not null);

insert into t
select i, i::text::bytea, i::text
from generate_series(1, 10000000) i;

create index t1 on t(f1);
create index t2 on t(f2);
create index t3 on t(f3);

Voyons à quelle vitesse chaque opération d'indexage s'est effectuée.

EntierByteaTexte
7 s18 s40 s

Alors, qu'indexer une colonne d'entiers soit plus rapide qu'une colonne de texte, ça ne me paraît pas choquant. En revanche, que le texte prenne plus de 2 fois le temps du binaire, voilà qui est surprenant !

Je n'ai pas d’explication à ce phénomène. Cependant, l'on notera que la différence principale entre le binaire et le texte est la présence d'un encodage. Celui-ci a certainement un impact lors de la création de l'index. Peut-être que Postgres revérifie que chaque chaîne est conforme à l'encodage ?

La bonne nouvelle, c'est qu'une fois l'index créé, le scan est aussi rapide pour les deux colonnes. Pas de quoi s'inquiéter, donc, mais à savoir lorsque l'on a besoin d'indexer quelques milliards de lignes avant Lundi matin.

Justement, parlons-en. Comment donc mettre à jour et indexer notre table efficacement ? Une approche qui fonctionne bien sur les machines multi-cœur est de supprimer l'héritage pour chaque table enfant, supprimer les index, et mettre à jour la table. Une fois l'opération complétée pour chaque table enfant, l'on peut recréer les index en parallèle : en effet, il est possible de créer des index en même temps sur différentes tables, et l'opération étant principalement limitée en CPU, l'on se retrouvera avec un temps divisé d'autant. Puis, l'on peut rattacher chaque sous-table à la table parent, et c'est reparti. L'on pensera à analyser les tables, car la mise à jour d'un type de colonne efface les statistiques.

2 commentaires:

Dongorath a dit…

Je ne connais pas Postgres, mais en basant sur ce que je sais de MS SQL Server, je vois 2 possibilités :
1) La collation. Les règles de comparaison de chaînes sont plus complexe dans le cas d'une chaîne de caractères avec collation que celles pour un tableau binaire.
2) Peut-être vérifier la taille (mémoire) des index. La taille de stockage du texte et d'une version "bytea" diffèrent peut-être.

Une autre possibilité, mais là, c'est vraiment du pif : sur SQL Server, le type "text" est en fait un pointeur vers une zone de texte stockée ailleurs, du coup, le coups d'accès au texte est supérieur à une valeur qui est stockée directement dans la page de la ligne.

M87 a dit…

Je soupçonne fort la collation, mais j'aimerais bien en avoir le cœur net. Cette histoire continue à me travailler, la prochaine fois que j'ai un SQL Server sous la main, je ferai la même expérience.