Est-il préférable de créer un index avant de remplir une table avec des données, ou après que les données soient en place ?

Question

J'ai une table d'environ 100 millions de lignes que je vais copier pour la modifier, en ajoutant un index. Ce n'est pas tant le temps nécessaire à la création de la nouvelle table qui m'inquiète, mais l'index créé sera-t-il plus efficace si je modifie la table avant d'insérer des données ou si j'insère d'abord les données puis ajoute l'index ?

Answer 1

La création d'un index après l'insertion des données est plus efficace (il est même souvent recommandé de supprimer l'index avant l'importation par lots et de le recréer après l'importation).

Exemple syntaxique (PostgreSQL 9.1, machine de développement lente, un million de lignes) :

CREATE TABLE test1(id serial, x integer);
INSERT INTO test1(id, x) SELECT x.id, x.id*100 FROM generate_series(1,1000000) AS x(id);
-- Time: 7816.561 ms
CREATE INDEX test1_x ON test1 (x);
-- Time: 4183.614 ms

Insérer puis créer un index - environ 12 sec

CREATE TABLE test2(id serial, x integer);
CREATE INDEX test2_x ON test2 (x);
-- Time: 2.315 ms
INSERT INTO test2(id, x) SELECT x.id, x.id*100 FROM generate_series(1,1000000) AS x(id);
-- Time: 25399.460 ms

Création d'un index, puis insertion - environ 25,5 secondes (plus de deux fois plus lent)

Answer 2

Il est probablement préférable de créer l'index après l'ajout des lignes. Non seulement ce sera plus rapide, mais l'équilibrage de l'arbre sera probablement meilleur.

Editar "équilibrer" n'est probablement pas le meilleur choix de termes ici. Dans le cas d'une b-tree, elle est équilibrée par définition. Mais cela ne signifie pas que la disposition de l'arbre b est optimale. La répartition des nœuds enfants au sein des parents peut être inégale (ce qui entraîne des coûts supplémentaires lors des futures mises à jour) et la profondeur de l'arbre peut être plus importante que nécessaire si l'équilibrage n'est pas effectué avec soin lors des mises à jour. Si l'index est créé après l'ajout des lignes, il est plus probable que la distribution soit meilleure. En outre, les pages d'index sur le disque peuvent présenter moins de fragmentation après la construction de l'index. Un peu plus d'informations ici

Answer 3

Cela n'a pas d'importance pour ce problème car :

1. Si vous ajoutez d'abord les données à la table et ensuite l'index. Le temps de génération de votre index sera O(n*log(N)) plus longtemps (où n est une rangée ajoutée). Comme le temps de germination des arbres est O(N*log(N)) alors si on divise ça en anciennes et nouvelles données, on obtient O((X+n)*log(N)) cela peut être simplement converti en O(X*log(N) + n*log(N)) et dans ce format, vous pouvez simplement voir ce que vous attendrez de plus.
2. Si vous ajoutez l'index et après cela, mettez les données. Chaque ligne (vous avez n nouvelles rangées) vous obtenez un temps supplémentaire d'insertion plus long O(log(N)) nécessaire pour régénérer la structure de l'arbre après l'ajout d'un nouvel élément (colonne d'index de la nouvelle ligne, car l'index existe déjà et la nouvelle ligne a été ajoutée, alors l'index doit être régénéré pour équilibrer la structure, ce coût O(log(P)) donde P est une puissance d'indice [éléments dans l'index] ). Vous avez n de nouvelles rangées, puis finalement vous avez n * O(log(N)) puis O(n*log(N)) résumé temps supplémentaire.

Answer 4

Les index créés après sont beaucoup plus rapides dans la plupart des cas. Exemple concret : 20 millions de lignes avec du texte complet sur varchar(255) - (Nom de l'entreprise) Index en place pendant l'importation des lignes - un match contre qui prend jusqu'à 20 secondes dans le pire des cas. Suppression de l'index et recréation - la comparaison prend moins d'une seconde à chaque fois.

Answer 5

Je ne suis pas sûr que cela soit vraiment important pour l'efficacité de l'index, puisque dans les deux cas vous insérez de nouvelles données dans l'index. Le serveur ne saurait pas à quel point un index est déséquilibré avant qu'il ne soit construit, en fait. Pour ce qui est de la vitesse, évidemment, faites les insertions sans l'index.