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.

Entier	Bytea	Texte
7 s	18 s	40 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.