Discussion :

[moumoune65]

Bonjour,
j'ai un gros problème concernant une base de données sur laquelle je travaille.
Il s'agit d'une base relativement simple (une trentaine de tables) avec une volumétrie conséquente (entre 200 000 et 1 000 000 d'enregistrement).
Lors d'une procédure de maintenance, je suis amené à supprimer un grand volume de données sur ces tables (environs 10 000 enregistrements pour certaines tables).
Malheureusement, le temps de suppression est trop long (plus d'une heure parfois) et cela risque d'empirer avec le volume croissant des données.
J'ai testé la suppression des index mais cela n'a rien changé.
Quelqu'un aurait-il une solution?
Merci

[kain_tn]

As-tu regardé du côté des opérations de maintenance comme VACUUM?

Après cela peut aussi dépendre de la requête que tu joues (DELETE avec conditions etc).

Pourrais-tu poster cette requête ici et poster également le résultat de cette même requête jouée avec les mots clés EXPLAIN ANALYSE en amont?

[punkoff]

parler de volumétrie sans parler de taille physique ca ne veux rien dire du tout.

1million d'enregistrements ca peut faire 10 mo comme quelques Go.

Bref, si l'explain donne rien il faudrai voir aussi si pendant vos actions de maintenances vous n'avez pas des actions concurentes qui pourraient locker vos table, ou si vous êtes cpu / io bound.

[SQLpro]

Contrairement à une idée reçue, retirez TOUS les index d'une table pour faire un DELETE de certaines lignes n'est pas le plus performant.

Comme toute opération de mise à jour, elle commence par une recherche positionnelle des tuples à traiter, tant est si bien qu'un index bien placé aide à réaliser la chose.

A +

[moumoune65]

Après analyse, voici les résultats sur une table qui contient pour le moment 290 000 enregistrements.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
explain analyze 
 delete from non_bati_a10_descr_parcelle where id_fantoir_commune = 310240;
 
"Delete  (cost=34.40..2499.18 rows=1304 width=6) (actual time=0.071..0.071 rows=0 loops=1)"
"  ->  Bitmap Heap Scan on non_bati_a10_descr_parcelle  (cost=34.40..2499.18 rows=1304 width=6) (actual time=0.071..0.071 rows=0 loops=1)"
"        Recheck Cond: (id_fantoir_commune = 310240)"
"        ->  Bitmap Index Scan on ndx_recherche_parcelle  (cost=0.00..34.07 rows=1304 width=0) (actual time=0.068..0.068 rows=0 loops=1)"
"              Index Cond: (id_fantoir_commune = 310240)"
"Total runtime: 0.114 ms"

A titre d'information, une requête comme cela mets plusieurs dizaines de minutes à s'exécuter.

Alors ma question est aussi de savoir si le fait qu'il y ait des liaisons avec cette table (foreign key) est-il un frein à la performance?

[estofilo]

D'après EXPLAIN ANALYZE (voir dernière ligne) la requête a mis 0.114ms à s'exécuter.
Ce n'est donc pas celle-là qui montrera où passent les dizaines de minutes en question.

Alors ma question est aussi de savoir si le fait qu'il y ait des liaisons avec cette table (foreign key) est-il un frein à la performance?

Oui la présence de FOREIGN KEY vers cette table ajoute du temps d'exécution puisqu'il faut vérifier s'il y avait des enregistrements ailleurs qui pointaient sur les lignes à effacer.

[SQLpro]

Avez vous conservé un index sur la colonne id_fantoir_commune pour le DELETE ?

A +

[moumoune65]

Bonjour,
après quelques mois d'utilisations, j'ai toujours quelques problèmes sur les suppressions massives.
Sur la requête

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
explain analyze 
delete from lot_a30_descr_lot
using  lot_a10_pdl
WHERE lot_a30_descr_lot.id_lot_a10_pdl = lot_a10_pdl.id
and lot_a10_pdl.id_fantoir_commune = 320013;

j'ai le résultat suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
"Delete  (cost=70.62..1122.58 rows=7261 width=12) (actual time=111.251..111.251 rows=0 loops=1)"
"  ->  Hash Join  (cost=70.62..1122.58 rows=7261 width=12) (actual time=1.094..21.402 rows=9347 loops=1)"
"        Hash Cond: (lot_a30_descr_lot.id_lot_a10_pdl = lot_a10_pdl.id)"
"        ->  Seq Scan on lot_a30_descr_lot  (cost=0.00..848.34 rows=34934 width=10) (actual time=0.007..11.248 rows=34934 loops=1)"
"        ->  Hash  (cost=63.61..63.61 rows=561 width=10) (actual time=0.501..0.501 rows=561 loops=1)"
"              Buckets: 1024  Batches: 1  Memory Usage: 20kB"
"              ->  Bitmap Heap Scan on lot_a10_pdl  (cost=16.60..63.61 rows=561 width=10) (actual time=0.121..0.326 rows=561 loops=1)"
"                    Recheck Cond: (id_fantoir_commune = 320013)"
"                    ->  Bitmap Index Scan on ndx_lot_a10_pdl_id_fantoir_commune  (cost=0.00..16.46 rows=561 width=0) (actual time=0.101..0.101 rows=561 loops=1)"
"                          Index Cond: (id_fantoir_commune = 320013)"
"Trigger for constraint fk_lotlocal_lota30descrlot: time=1010.358 calls=9347"
"Trigger for constraint fk_nonbatia21descrsuf_lota30descrlot: time=2311695.025 calls=9347"
"Total runtime: 2312835.032 ms"

Il semblerai donc que la suppression soit très lente au niveau des contraintes de clés étrangères.

[SQLpro]

Avez vous mis des contraintes FK en DELETE CASCADE ?

A +

[estofilo]

Manifestement c'est le contrôle de cette FK qui prend tout le temps:

Trigger for constraint fk_nonbatia21descrsuf_lota30descrlot: time=2311695.025 calls=9347

Il faudrait voir quelle colonne est la source de la FK et l'indexer si elle ne l'est pas.

[SQLpro]

Oui, mais si c'est du delete cascade, cela journalise aussi les delete des tables filles. D'ou ma question....

A +

[moumoune65]

Après vérification, mes FK ne sont pas en delete cascade.
J'avais privilégié cette option au début pour supprimer mes enregistrements à la chaîne mais c'était catastrophique en terme de performance.
Deuxième point, je n'avais pas d'indexation sur mes FK (pour tout avouer, je pensais que PostgreSQL générait un index pour chaque FK comme il le fait pour les PK).Là, le gain de temps est considérable et mes lignes "Trigger for constraint " ont disparues lors de l'analyse de la requête.

Je pense donc que le problème est résolu!

J'ai encore deux questions :
- je suis donc dans l'obligation d'indexer mes FK. Cela n'est-il pas plus lourd que de supprimer mes contraintes FK avant mes mises à jour (M. SQLPRO, j'ai vu que vous avez fait cela pour les index!)
- Postgresql propose deux types d'index : BTREE ou HASH. Sachant que je n'ai pas d'index unique à mettre en place et que l'accès à cet index se fait par un opérateur d'égalité, doit-je privilégier le HASH?

Merci encore

[estofilo]

Après vérification, mes FK ne sont pas en delete cascade.
J'avais privilégié cette option au début pour supprimer mes enregistrements à la chaîne mais c'était catastrophique en terme de performance.

C'est peut-être juste parce qu'il n'y avait les index qu'il fallait. Le DELETE comme la contrainte simple ont besoin tous les deux de trouver rapidement les enregistrements concernés.

- je suis donc dans l'obligation d'indexer mes FK. Cela n'est-il pas plus lourd que de supprimer mes contraintes FK avant mes mises à jour (M. SQLPRO, j'ai vu que vous avez fait cela pour les index!)

Indexer les FK est plutôt la norme. C'est une bonne idée de ne pas abuser des index mais ceux qui sont nécessaires, il faut les mettre.
Sinon le fait de supprimer les contraintes et les remettre après est coûteux puisqu'à la recréation elle vont être retestées sur l'intégralité du contenu.

- Postgresql propose deux types d'index : BTREE ou HASH. Sachant que je n'ai pas d'index unique à mettre en place et que l'accès à cet index se fait par un opérateur d'égalité, doit-je privilégier le HASH?

L'index hash pourrait être une bonne option pour un index temporaire avant un traitement batch parce qu'il est plus rapide à créer et souvent plus petit. Le revers de la médaille est qu'il n'est pas WAL-loggé donc ne résiste pas à un crash de la base (mais en usage temporaire ce n'est pas un problème).

En performance d'accès les tests dispos sur le web ont l'air de montrer que ça ne diffère pas beaucoup du b-tree.
Voir par exemple: http://www.depesz.com/2010/06/28/sho...se-hash-index/

[moumoune65]

Merci pour ces réponses!

[SQLpro]

Deuxième point, je n'avais pas d'indexation sur mes FK (pour tout avouer, je pensais que PostgreSQL générait un index pour chaque FK comme il le fait pour les PK).

Aucun SGBDR d'aucune sorte n'index automatiquement les FK car il y a des cas (héritage, liens identifiant, tables de jointure) oui ces FK sont déjà indexée, voire partiellement indexées (index inclus).

Un outil de modélisation comme Power AMC n'aurait pas commis cette faute !

J'ai encore deux questions :
- je suis donc dans l'obligation d'indexer mes FK. Cela n'est-il pas plus lourd que de supprimer mes contraintes FK avant mes mises à jour (M. SQLPRO, j'ai vu que vous avez fait cela pour les index!)

Non, ne supprimez pas vos contraintes ni les index sous FK. Il faudrait TOUT revérifier à la remise en place !!!

- Postgresql propose deux types d'index : BTREE ou HASH. Sachant que je n'ai pas d'index unique à mettre en place et que l'accès à cet index se fait par un opérateur d'égalité, doit-je privilégier le HASH?

les index en HASH sont, en pratique, une belle merde ! Dans la 4e édition de mon livre sur SQL, j'ai ajouté un chapitre sur l'indexation et j'en parle en les déconseillant fortement !

Par exemple les index en hash ne peuvent pas être utilisé pour d'autres opérateurs que = et donc ne peuvent pas servir pour un ORDER BY, entre autres...

Lisez au moins la doc :
https://postgresql.developpez.com/do...tion/francais/

"
Note

Les tests ont montré que les index hash de PostgreSQL™ ne réalisent pas mieux que les index B-tree. La taille de l'index et son temps de construction sont bien pires. De plus, les opérations d'index hash ne sont pas tracées par les WAL, donc les index hash peuvent avoir besoin d'être reconstruit avec REINDEX après un crash de la base. Pour ces raisons, l'utilisation des index hash est découragée.
"

L'exemple donné par despez est biaisé par le fait que la table n'a qu'une seule colonne, il n'y a pas de clef primaire et que les données sont toutes quasiment de même longueur. Enfin aucun test d'insertion n'a été mené !

A +