Temps d'execution de requête très long

Bonjour à tous,

Voila j'ai une table dans ma base Postgres qui contient plus de 6 millions de lignes.
Mais une simple requête update par exemple prend plus de 30 min.
Exemple

Code:

---

update reprise_journal r set id_agen = a.id from agen a WHERE r.code = a.code and statut in ('A' , 'B');

---

met 32 min

Code:

---

select * from reprise_journal where statut in ('A' , 'B');

---

met 1 min 50 secs

J'ai meme créé des Indes dans les deux table:
idx_reprise_journal_code
idx_reprise_journal_statut
idx_agen_code

J'aimerai vraiment avoir un tuyau pour l'optimisation d'une table postgres avec des millions de données.

Je vous remercie d'avance

Si tu veux comparer les temps d'exécution, il faut comparer la requête UPDATE avec celle-ci :

Code:

---

1 2 3 4 5 6 7

select * from reprise_journal r inner join agen a on r.code = a.code where statut in ('A' , 'B') ;

---

Et c'est sur cette dernière requête que devrait porter ton optimisation...

Que donne le temps d'exécution de ces 2 requêtes prises séparément :

Code:

---

1 2	update reprise_journal r set id_agen = a.id from agen a WHERE r.code = a.code and statut = 'A'; update reprise_journal r set id_agen = a.id from agen a WHERE r.code = a.code and statut = 'B';

---

D'autre part, d’où vient la colonne "statut" da la table "reprise_journal" ou "agen"

Si c'est de de la table "agent, créez l'index suivant s'il n'existe pas déjà :

Code:

---

CREATE INDEX X001 ON agen (statut, code) WITH (fillfactor = 70);

---

Sinon, assurez vous que la table "reprise_journal" contient l'index suivant :

Code:

---

CREATE INDEX X001 ON reprise_journal (statut, code) WITH (fillfactor = 70);

---

A +

Bonjour à tous,

Vraiment désolée d'avoir mis du temps à répondre. Nous avons dû partir en team building avec mes collègues ce weekend.

Bon pour répondre à SQLpro, le temps d'exécution de la requête:

Code:

Code:

---

update reprise_journal r set id_agen = a.id from agen a WHERE r.code = a.code and statut = 'A';

---

est de 30 min 46 sec.

Code:

---

update reprise_journal r set id_agen = a.id from agen a WHERE r.code = a.code and statut = 'B';

---

est de 30 sec vu qu'il n'ya pas encore de statut B.

Le statut est sur la table reprise_journal et j'ai bien créé l'index.

Et pour répondre à al1_24, les temps d'exécution de:

Code:

Code:

---

1 2 3 4 5 6 7

select * from reprise_journal r inner join agen a on r.code = a.code where statut in ('A' , 'B') ;

---

est de 1min 37 sec.

Bonjour à tous,

Je relance si quelqu'un à des propositions sur l'optimisation de ma base Postgres (notamment au niveau de la gestion des index...)

Merci d'avance

(1) Avec une telle volumétrie un UPDATE sera lent si id_gen dispose ou fait partie d'un index, parce que pour toute mise à jour, il faudra recalculer l'index.
En espérant que ce n'est pas le cas.

Combien de lignes retourne cette requête ?

Code:

---

1 2 3 4 5

SELECT COUNT(*) FROM reprise_journal r INNER JOIN agen a on r.code = a.code WHERE statut in ('A' , 'B')

---

(2) Faire Un VACUUM sur la table reprise_journal pour faire le menage, surtout si des mises à jour sont faites régulièrement dessus.

Code:

---

VACUUM FULL reprise_journal

---

https://www.postgresql.org/docs/9.5/sql-vacuum.html

(2) Essayez aussi la commande EXPLAIN pour analyser la requête

Code:

---

1 2 3 4 5

EXPLAIN ANALYZE UPDATE reprise_journal r SET id_agen = a.id from agen a WHERE r.code = a.code and statut in ('A' , 'B');

---

Merci manzeki

Code:

---

1 2 3 4

SELECT COUNT(*) FROM reprise_journal r INNER JOIN agen a on r.code = a.code WHERE statut in ('A' , 'B')

---

retourne 6552509 lignes.

Pour les index, Je ne sais pas si les foreign key sont inclus car id_agen est présent dans plusieurs tables (comme foreign key).

L'analyse de la requête :

Code:

---

1 2 3 4

EXPLAIN ANALYZE UPDATE reprise_journal r SET id_agen = a.id from agen a WHERE r.code = a.code and statut in ('A' , 'B');

---

fait: 14 min 55 secs

Et voici le résultat:

Citation:

---

Update on reprise_journal r (cost=1.11..399269.15 rows=6552509 width=7559) (actual time=894642.757..894642.757 rows=0 loops=1)
-> Hash Join (cost=1.11..399269.15 rows=6552509 width=7559) (actual time=0.506..26311.160 rows=6552509 loops=1)
Hash Cond: ((r.code)::text = (a.code)::text)
-> Seq Scan on reprise_journal r (cost=0.00..367488.36 rows=6552509 width=7516) (actual time=0.441..19964.590 rows=6552509 loops=1)
Filter: ((statut)::text = ANY ('{A,B}'::text[]))
-> Hash (cost=1.05..1.05 rows=5 width=48) (actual time=0.020..0.020 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory UAge: 9kB
-> Seq Scan on agen a (cost=0.00..1.05 rows=5 width=48) (actual time=0.007..0.009 rows=5 loops=1)
Planning time: 7.618 ms
Execution time: 894643.552 ms

---

HASH JOIN et SEQ SCAN indique qu'il n'utilise pas les index ni pour la jointure ni pour la "lecture".

Mais comme vous mettez à jour toutes les lignes c'est relativement normal….
De plus, PostgreSQL ne sait pas faire de parallélisme… sauf sur quelques broutilles et uniquement pour le SELECT.

Si vous aviez des filtres supplémentaire pour éviter la mise à jour des lignes inutiles, ça irait plus vite !

A +

Suggestions :

Si déclaré comme foreign key, toute modification (UPDATE, DELETE) sur id_gen entraîne
- une vérification d'intégrité dans toutes les tables déclarant id_gen comme clé étrangère
- et une mise à jour des valeurs de id_gen dans toutes les tables déclarant id_gen comme clé étrangère avec une option CASCADE.

Le coût en performance sera conséquente.

Faites le test suivant :
A défaut de désactiver les cléfs étrangères comme dans mysql,

Créez une colonne id_gen2 de même type que id_gen, sans index ni foreign key
Au pire passez par une table temporaire reprise_journal2 sans index sur id_gen et veuillez à ce que id_gen ne soit une clé étrangère.

Exécutez la mise à jour, puis notez le temps d'exécution :

Code:

---

1 2 3

UPDATE reprise_journal r SET id_agen2 = a.id from agen a WHERE r.code = a.code and statut in ('A' , 'B');

---

Si le temps est court, alors:
(1) faites un script qui supprime les clés étrangères et les index sur id_gen,
(2) Exécuter la mise à jour
(3) Recréer les indexes et les clés étrangères

Reflexion :
Le nombre de lignes à modifier est excessif, je me demande quelle sont les raisons d'une telle mise à jour,
- est-ce une opération occasionnelle (maintenance par exemple) ?
- ou une mise à jour faite régulièrement ?

bonjour

Citation:

---

Envoyé par tibia76

Voila j'ai une table dans ma base Postgres qui contient plus de 6 millions de lignes.
Mais une simple requête update par exemple prend plus de 30 min.

---

Citation:

---

Envoyé par tibia76

Merci manzeki

Code:

---

1 2 3 4

SELECT COUNT(*) FROM reprise_journal r INNER JOIN agen a on r.code = a.code WHERE statut in ('A' , 'B')

---

retourne 6552509 lignes.

---

Donc, l'update concerne la quasi totalité des lignes de la table, autant dire que l'index ne sert à rien en terme de filtrage.
du coup, le select comme l'update font un tablescan
Mais l'update doit modifier les index multiples sur la colonne statut en plus des data, ce qui fait d'autant plus de MàJ
S'il y a 2 index multiples incluant cette colonne et qu'il y a en moyenne 3 occurrences par valeur, ça fait 18 millions de mises à jour supplémentaires
Et comme cet index n'est très probablement pas cluster, il y a plein d'allers-retours afin de récupérer les pages concernées pour effectuer ce travail !

Une solution a tester est de supprimer les index non sémantiques de la table et de les reconstruire après l'UPDATE. Cela est généralement plus rapide.

A +