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

  1. #1
    Nouveau Candidat au Club
    Femme Profil pro
    Développeur informatique
    Inscrit en
    juin 2018
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : Sénégal

    Informations professionnelles :
    Activité : Développeur informatique

    Informations forums :
    Inscription : juin 2018
    Messages : 4
    Points : 1
    Points
    1

    Par défaut 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
    Avatar de al1_24
    Homme Profil pro
    Ingénieur d'études décisionnel
    Inscrit en
    mai 2002
    Messages
    8 140
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 58
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Ingénieur d'études décisionnel
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 8 140
    Points : 26 266
    Points
    26 266

    Par défaut

    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...
    Modérateur Langage SQL
    Règles du forum Langage SQL à lire par tous, N'hésitez pas à consulter les cours SQL
    N'oubliez pas le bouton et pensez aux balises
    [code]
    Si une réponse vous a aidé à résoudre votre problème, n'oubliez pas de voter pour elle en cliquant sur
    Aide-toi et le forum t'aidera : Un problème exposé sans mentionner les tentatives de résolution infructueuses peut laisser supposer que le posteur attend qu'on fasse son travail à sa place... et ne donne pas envie d'y répondre.

  3. #3
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    19 046
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 19 046
    Points : 44 876
    Points
    44 876

    Par défaut

    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  4. #4
    Nouveau Candidat au Club
    Femme Profil pro
    Développeur informatique
    Inscrit en
    juin 2018
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : Sénégal

    Informations professionnelles :
    Activité : Développeur informatique

    Informations forums :
    Inscription : juin 2018
    Messages : 4
    Points : 1
    Points
    1

    Par défaut

    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
    Femme Profil pro
    Développeur informatique
    Inscrit en
    juin 2018
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : Sénégal

    Informations professionnelles :
    Activité : Développeur informatique

    Informations forums :
    Inscription : juin 2018
    Messages : 4
    Points : 1
    Points
    1

    Par défaut 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
    Profil pro
    Ingénieur développement logiciels
    Inscrit en
    mai 2008
    Messages
    129
    Détails du profil
    Informations personnelles :
    Localisation : Canada

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : Industrie

    Informations forums :
    Inscription : mai 2008
    Messages : 129
    Points : 220
    Points
    220

    Par défaut 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/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');

  7. #7
    Nouveau Candidat au Club
    Femme Profil pro
    Développeur informatique
    Inscrit en
    juin 2018
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : Sénégal

    Informations professionnelles :
    Activité : Développeur informatique

    Informations forums :
    Inscription : juin 2018
    Messages : 4
    Points : 1
    Points
    1

    Par défaut

    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
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    19 046
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 19 046
    Points : 44 876
    Points
    44 876

    Par défaut

    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

Discussions similaires

  1. [2008] SQL Server Linked Oracle9i : résultat de la requête très long
    Par masterdash dans le forum Développement
    Réponses: 2
    Dernier message: 08/02/2019, 10h12
  2. Temps de chargement flux audio très long
    Par Marc GA dans le forum Android
    Réponses: 5
    Dernier message: 26/06/2012, 21h29
  3. temps d'execution entre requète DB et affichage
    Par baboutom dans le forum Windows Presentation Foundation
    Réponses: 5
    Dernier message: 27/05/2009, 00h27
  4. Réponses: 1
    Dernier message: 30/06/2008, 17h01
  5. optimisation de temps d'execution de requête
    Par Smix007 dans le forum SQL
    Réponses: 7
    Dernier message: 21/06/2007, 17h49

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo