Discussion :

[tibia76]

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 :

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

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 :

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

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

[al1_24]

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

Code :

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

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...

[SQLpro]

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

Code :

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

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 :

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

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

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

Code :

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

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

A +

[tibia76]

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 :

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

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 :

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

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 :

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

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.

[tibia76]

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

[manzeki]

(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 :

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

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 :

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

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 :

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

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');

[tibia76]

Merci manzeki

Code :

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

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 :

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

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:

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

[SQLpro]

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 +

[manzeki]

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 :

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

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 ?

[escartefigue]

bonjour

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.

Merci manzeki

Code :

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

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 !

[SQLpro]

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 +