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
    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 195
    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 195
    Points : 26 469
    Points
    26 469
    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
    YYYY
    Inscrit en
    mai 2002
    Messages
    19 141
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : YYYY
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 19 141
    Points : 45 203
    Points
    45 203
    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
    Administrateur
    Inscrit en
    mai 2008
    Messages
    138
    Détails du profil
    Informations personnelles :
    Localisation : Canada

    Informations professionnelles :
    Activité : Administrateur
    Secteur : Industrie

    Informations forums :
    Inscription : mai 2008
    Messages : 138
    Points : 241
    Points
    241
    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
    YYYY
    Inscrit en
    mai 2002
    Messages
    19 141
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : YYYY
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 19 141
    Points : 45 203
    Points
    45 203
    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 * * * * *

  9. #9
    Membre actif
    Profil pro
    Administrateur
    Inscrit en
    mai 2008
    Messages
    138
    Détails du profil
    Informations personnelles :
    Localisation : Canada

    Informations professionnelles :
    Activité : Administrateur
    Secteur : Industrie

    Informations forums :
    Inscription : mai 2008
    Messages : 138
    Points : 241
    Points
    241
    Par défaut
    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

    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    4 902
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 4 902
    Points : 13 816
    Points
    13 816
    Billets dans le blog
    1
    Par défaut
    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
    Avatar de SQLpro
    Homme Profil pro
    YYYY
    Inscrit en
    mai 2002
    Messages
    19 141
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : YYYY
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 19 141
    Points : 45 203
    Points
    45 203
    Par défaut
    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 +
    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