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 ?

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 +

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

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

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 ?

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...).
@+

Citation:

---

Envoyé par Aizen64

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 +

Citation:

---

Envoyé par ced

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 ?

Citation:

---

Envoyé par Aizen64

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/

Citation:

---

Du paramétrage de mémoire à faire, ou autre ?

---

Avoir une RAM = volume total de la base....

A +

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 :)

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.
@+