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