Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Requêtes PostgreSQL Discussion :

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


Sujet :

Requêtes PostgreSQL

  1. #1
    Nouveau Candidat au Club
    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 :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

  2. #2
    Modérateur

    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...
    Cette signature n'a pas pu être affichée car elle comporte des erreurs.

  3. #3
    Rédacteur

    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 +
    Cette signature n'a pas pu être affichée car elle comporte des erreurs.

  4. #4
    Nouveau Candidat au Club
    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.

  5. #5
    Nouveau Candidat au Club
    Up
    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

  6. #6
    Membre actif
    Quelques pistes d'optimisations
    (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/...ql-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');

  7. #7
    Nouveau Candidat au Club
    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

  8. #8
    Rédacteur

    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 +
    Cette signature n'a pas pu être affichée car elle comporte des erreurs.

  9. #9
    Membre actif
    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 ?

  10. #10
    Expert éminent sénior
    bonjour

    Citation Envoyé par tibia76 Voir le message
    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 Voir le message
    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 !

  11. #11
    Rédacteur

    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 +
    Cette signature n'a pas pu être affichée car elle comporte des erreurs.