1. #1
    Membre averti Avatar de Aizen64
    Profil pro
    Inscrit en
    mai 2007
    Messages
    479
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : mai 2007
    Messages : 479
    Points : 367
    Points
    367

    Par défaut Forcer l'utilisation d'index

    Bonjour,

    j'ai un sérieux souci de performance sur l'une de mes vues qui contient 16 tables, j'ai bien fait en sorte d'éliminer tous les LEFT JOIN inutiles et pourtant, une requête qui doit me retourner 2 lignes avec une dizaine de champs, met, en local, 800 à 900 ms à s’exécuter.

    Quelques infos de config :
    - version : 9.4
    - OS : Debian 8
    - nombre de connexions actives : ~2.
    - Hôte : localhost
    - pg_stat_activity : 4 lignes donc charge du SGBD quasi nulle.

    Un EXPLAIN ANALYZE montre clairement que ce sont les jointures qui prennent du temps, sur certaines tables, je comprends le choix d'un scan séquentiel quand lesdites tables ont peu de données, cependant, même sur des tables contenant plusieurs milliers de lignes, Postgres fait du scan séquentiel sur la plupart des tables, seulement 3 utilisent un index.

    Alors, j'ai effectué plusieurs tâches de maintenance :
    - VACCUM ANALYZE pour mettre à jour les stats,
    - REINDEX DATABASE pour mettre à jour les index.

    Malgré cekui Postgres ne change pas son plan d'exécution, en conséquence, dans des conditions réelles entre 2 serveurs distants dont une BDD postgres qui a une charge, cette même requête peut être plus de 2 fois plus lente que mes tests en localhost.

    C'est probablement considéré comme une mauvaise pratique, seulement, je n'ai pas vu de fonctionnalité équivalente aux HINTS de Oracle pour forcer l'utilisation d'index.

    Ma question est, est-il possible, au moins à des fins de tests de forcer l'utilisation d'index afin d'optimiser les performances ?

    A celles/ceux qui répondront, oubliez une seconde que forcer l'utilisation d'index est une mauvaise pratique. Auriez-vous des solutions alternatives ?
    Exprimer une différence d'opinion vaut mieux que :

  2. #2
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    16 955
    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 : 16 955
    Points : 39 360
    Points
    39 360
    Billets dans le blog
    1

    Par défaut

    PostGreSQL ne permet pas de forcer les plans de requête, sauf à utiliser la version EnterpriseDB... (1 500 $ par CPU).

    L'optimiseur est réglé par défaut pour optimiser jusqu'à 12 jointures si mes souvenirs sont exacts. Au delà il utilise un truc censé être intelligent, mais qui en pratique s'avère souvent incapable de faire du bon boulot (geqo)...

    Tentez de modifier le fichier de config pour optimiser jusqu'à 20 jointures (geqo_threshold), voire ce que cela donne.

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

  3. #3
    ced
    ced est déconnecté
    Rédacteur/Modérateur

    Avatar de ced
    Homme Profil pro
    Gestion de bases de données techniques
    Inscrit en
    avril 2002
    Messages
    5 529
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 41
    Localisation : France, Loiret (Centre)

    Informations professionnelles :
    Activité : Gestion de bases de données techniques
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : avril 2002
    Messages : 5 529
    Points : 21 603
    Points
    21 603

    Par défaut

    Bonjour,

    On peut aussi se poser la question autrement : pourquoi est-ce que PostgreSQL n'utilise pas un index donné (plutôt que de le forcer) ?
    Mais sans le plan d'exécution de votre requête, c'est impossible à dire...
    Rédacteur / Modérateur SGBD et R
    Mes tutoriels et la FAQ MySQL

    ----------------------------------------------------
    Pensez aux balises code et au tag
    Une réponse vous a plu ? N'hésitez pas à y mettre un
    Je ne réponds pas aux questions techniques par message privé, les forums sont là pour ça

  4. #4
    Expert éminent

    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    2 729
    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 : 2 729
    Points : 6 015
    Points
    6 015
    Billets dans le blog
    1

    Par défaut

    Essayez peut être (je n'y crois guère mais bon...) de découper la requête en tranches, en utilisant des CTE

  5. #5
    Membre averti Avatar de Aizen64
    Profil pro
    Inscrit en
    mai 2007
    Messages
    479
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : mai 2007
    Messages : 479
    Points : 367
    Points
    367

    Par défaut

    Bonjour Frédéric,

    merci pour la réponse, j'ai dont décommenté ces 2 lignes dans postgresql.conf :
    geqo = on
    geqo_threshold = 20

    Impact nul sur l'exécution de la requête, le plan d'éxécution de la requête fait du scan séquentiel au lieu d'utiliser des index. D'autres idées ?

    Side note personnelle : votre livre sur du SQL est toujours en cours de parution ?
    Exprimer une différence d'opinion vaut mieux que :

  6. #6
    Membre émérite
    Avatar de alassanediakite
    Homme Profil pro
    Recherche, formation, développement
    Inscrit en
    août 2006
    Messages
    1 416
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 39
    Localisation : Mali

    Informations professionnelles :
    Activité : Recherche, formation, développement

    Informations forums :
    Inscription : août 2006
    Messages : 1 416
    Points : 2 964
    Points
    2 964
    Billets dans le blog
    6

    Par défaut

    Salut
    Voici les paramètres permettant d'influencer le comportement du moteur de requête.
    Mais il est encore mieux de donner le SQL de la requête et le plan d'exécution réel (explain analyse...).
    @+
    Le monde est trop bien programmé pour être l’œuvre du hasard…
    Mon produit pour la gestion d'école: www.logicoles.com

  7. #7
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    16 955
    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 : 16 955
    Points : 39 360
    Points
    39 360
    Billets dans le blog
    1

    Par défaut

    Citation Envoyé par Aizen64 Voir le message
    Bonjour Frédéric,

    merci pour la réponse, j'ai dont décommenté ces 2 lignes dans postgresql.conf :
    geqo = on
    geqo_threshold = 20

    Impact nul sur l'exécution de la requête, le plan d'éxécution de la requête fait du scan séquentiel au lieu d'utiliser des index. D'autres idées ?

    Side note personnelle : votre livre sur du SQL est toujours en cours de parution ?
    Sur certaines versions il faut relancer le serveur pour appliquer les modifs.....

    Néanmoins, ne pensez pas que PostGreSQL soit l'équivalent d'Oracle ou mieux encore de SQL Server.... Au niveau optimisation il en est très loin.... Je voit de plus en plus de DSI migrer des gros serveurs critique vers PG et en revenir aussi sec...

    PG reste valable à 4 conditions :
    1) pas de gros volume (ne pas aller au dela de 300 Go)
    2) pas un grand nombre d'utilisateurs (ne pas aller au delà d'une centaine de connexions simultanées)
    3) ne pas avoir besoin de fonctionner 24h/24 à pleine charge (PostGreSQL est bloquant à cause de son MVCC et du VACUUM)
    4) ne pas avoir de requêtes complexes... En particulier ne pas dépasser une douzaine de table...

    Il lui manque encore beaucoup de chose pour être efficace... !

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

  8. #8
    Membre averti Avatar de Aizen64
    Profil pro
    Inscrit en
    mai 2007
    Messages
    479
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : mai 2007
    Messages : 479
    Points : 367
    Points
    367

    Par défaut

    Citation Envoyé par ced Voir le message
    Bonjour,

    On peut aussi se poser la question autrement : pourquoi est-ce que PostgreSQL n'utilise pas un index donné (plutôt que de le forcer) ?
    Mais sans le plan d'exécution de votre requête, c'est impossible à dire...
    Certes, je posterai le plan, le truc est que je dois faire en sorte de ne pas donner trop d'infos sur le modèle donc je vais devoir renommer les tables.

    @SQLPro pour l'efficacité de PG, aussi partisant que sont ces arguments comme MVP Microsoft, dans les faits, la BDD qu'on utilise en backend tient avec 100 utilisateurs, au delà j'aimerais bien voir la tenue en charge une fois mon projet lancer qui doit tenir plusieurs milliers d'utilisateurs. Avec un peu de chance, il y aura une charge répartie dans le temps et peu de pics d'utilisation.

    Dans le cas contraire, la base de prod tombera.

    Ça me fait doucement rire les partisants inconditionnels du libre, là en matière d'optimisation PG montre de sérieuses limites. Je ne dit pas que le SGBD est mauvais, juste, je trouve un peu facile de dire que SQL Server est de la merde parce MS est derrière.

    D'autres méthodes d'optimisation ? Est-ce que les parallel queries peuvent aider ?

    Du paramétrage de mémoire à faire, ou autre ?
    Exprimer une différence d'opinion vaut mieux que :

  9. #9
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    16 955
    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 : 16 955
    Points : 39 360
    Points
    39 360
    Billets dans le blog
    1

    Par défaut

    Citation Envoyé par Aizen64 Voir le message
    D'autres méthodes d'optimisation ? Est-ce que les parallel queries peuvent aider ?
    Le parallélisme dans PG reste embryonnaire. Seul quelques éléments (essentiellement le SCAN) sont parallélisés. Dans le benchmark que j'ai fait sur la partie SIG des deux SGBDR SQL Server et PG, on voit que SQL Sever parallélise 4 requêtes quand PG n'en parallélise qu'une seule. En quand PG parallélise cette requête sur mon PC doté de 48 cœurs, il divise le temps par 3, là ou SQL Server le divise par 8...
    http://g-ernaelsten.developpez.com/t...-performances/

    Du paramétrage de mémoire à faire, ou autre ?
    Avoir une RAM = volume total de la base....

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

  10. #10
    Membre émérite
    Avatar de alassanediakite
    Homme Profil pro
    Recherche, formation, développement
    Inscrit en
    août 2006
    Messages
    1 416
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 39
    Localisation : Mali

    Informations professionnelles :
    Activité : Recherche, formation, développement

    Informations forums :
    Inscription : août 2006
    Messages : 1 416
    Points : 2 964
    Points
    2 964
    Billets dans le blog
    6

    Par défaut Pour ceux qui veulent connaitre la vraie information!

    Salut
    Ce que dit un expert sur la gestion de la mémoire avec PostgreSQL (copier/coller le lien):http://www.dalibo.org/glmf107_gestio...vec_postgresql
    @+
    Le monde est trop bien programmé pour être l’œuvre du hasard…
    Mon produit pour la gestion d'école: www.logicoles.com

  11. #11
    Membre averti Avatar de Aizen64
    Profil pro
    Inscrit en
    mai 2007
    Messages
    479
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : mai 2007
    Messages : 479
    Points : 367
    Points
    367

    Par défaut

    J'ai essayé, à la louche de modifier le réglages de mémoire :
    - shared_buffers : de 128Mo à 512Mo
    - work_mem : 4Mo
    - enable_secscan = off

    Il fait des hash join, l'impact est nul sur les perfs.

    Même MySQL permet de forcer l'utilisation d'index, au moins pour des raisons de tests...

    Enfin, j'ai rien dit moi, enfin si, pgAdmin3 est... mauvais ? instable, lent et mal pensé ?

    Vous pouvez ignorer la dernière phrase, mauvaise journée
    Exprimer une différence d'opinion vaut mieux que :

  12. #12
    Membre émérite
    Avatar de alassanediakite
    Homme Profil pro
    Recherche, formation, développement
    Inscrit en
    août 2006
    Messages
    1 416
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 39
    Localisation : Mali

    Informations professionnelles :
    Activité : Recherche, formation, développement

    Informations forums :
    Inscription : août 2006
    Messages : 1 416
    Points : 2 964
    Points
    2 964
    Billets dans le blog
    6

    Par défaut

    Salut

    L'optimisation n'est pas uniquement au niveau du SGBD!
    On a besoin de la structure de vos tables et index, le sql de la requête et surtout le plan d’exécution.
    @+
    Le monde est trop bien programmé pour être l’œuvre du hasard…
    Mon produit pour la gestion d'école: www.logicoles.com

Discussions similaires

  1. Forcer l'utilisation d'un index (hint INDEX)
    Par s_markowski dans le forum Interfaces de programmation
    Réponses: 2
    Dernier message: 12/07/2010, 06h42
  2. Réponses: 1
    Dernier message: 01/03/2005, 21h36
  3. Compteur sur l'utilisation des index
    Par hkhan dans le forum Administration
    Réponses: 11
    Dernier message: 14/10/2004, 17h57
  4. Utilisation des "indexs" ?
    Par vandeyy dans le forum Décisions SGBD
    Réponses: 1
    Dernier message: 07/09/2004, 07h49
  5. Utilisation de Index Server
    Par Emmanuel.G dans le forum XMLRAD
    Réponses: 2
    Dernier message: 03/09/2003, 11h50

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