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.

[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