Agir sur les plans dans PostgreSQL au niveau DBA ?

[Phil]

Merci à un DBA Oracle pour sa question :

"Avec Oracle, je peux agir directement sur les plans d'exécution, est-ce possible avec PostgreSQL ?"

Réponse :

Pas simple comme question. Globalement OUI...mais différemment. Il y a beaucoup de discussions sur le sujet mais cela tourne souvent au dialogue de sourds car rares sont les DBA à vraiment maîtriser plusieurs SGBD. La tentation est de vouloir plaquer sur un SGBD qu'on ne connaît pas le raisonnement valable sur un autre.

Le DBA Oracle va dire "PostreSQL c'est nul, je ne peux pas modifier les plans en hintant les requêtes ."
Le DBA PostgreSQL va dire "Oracle c'est nul, les hints sont une source de problèmes de maintenance."

En fait, avec Oracle Database, hinter les requêtes au niveau DBA consiste à créer une outline. Cela date de la 8i mais c'est déprécié et ça n'existe quasi plus. Oracle est passé des outlines (ensemble de hints pour figer UN plan) à des outils plus évolués et plus complexes. Le SQL profile ne fige pas les plans, la baseline peut en accepter plusieurs.
Si l'applicatif est vraiment mal pensé pour la perf, le meilleur optimiseur ne pourra pas faire de miracle même avec des outils complémentaires pour ajuster les plans. Le mal est fait, le DBA ou le SGBD autonome ne peuvent que colmater.
En dehors du strict remplacement des outlines depuis la 18c, les SQL profiles et baselines sont uniquement disponibles en option d'Oracle Database Enteprise Edition (tuning & diagnostic pack). Il arrive que les outils de conseil (tuning advisors) ne trouvent rien et ne fassent aucune suggestion...peut-être parce qu'il n'y en a aucune à faire, peut-être en raison de leurs limitations.
OUILLE ! Vous revenez alors au point de départ, vos utilisateurs attendent et c'est à vous, DBA, de trouver une solution...et souvent de connaître l'applicatif et donc de discuter avec les utilisateurs et développeurs. Tom Kyte a insisté sur l'aspect fondamental de ce travail commun pendant des années, bien avant que le "devops" soit un concept formalisé. C'est la valeur ajoutée d'un DBA humain, le DBA qui se contente de travailler sur ses serveurs depuis sa tour d'ivoire est un dinosaure condamné à disparaître. Il pourra à terme être complètement remplacé par les capacités des bases autonomes.
Et s'il faut agir en urgence ? D'abord est-ce vraiment un problème de plan ? Supposons que oui et que vous ayez le "bon" plan. Si le mauvais plan est dû à un index manquant, il faut ajouter cet index. Des outils existent pour faire des suggestions, avec PostgreSQL comme avec Oracle.
Si vraiment le planner ne trouve pas de bon plan alors que vous savez qu'il en existe un, cela peut être comme avec Oracle un problème de statistiques (pensez aux statistiques étendues), mais aussi de déclaration des fonctions (volatile alors que la fonction est stable ou immutable)
En pratique, je n'ai jamais été coincé avec un "mauvais" plan alors que je savais qu'il en existait un meilleur et que PostgreSQL était capable de l'exécuter. Attention à ne pas confondre un problème d'optimiseur et une capacité manquante à l'exécution : inutile d'espérer faire de l'IndexOnlyScan avec d'anciennes versions de PostgreSQL comme la 8.4 par exemple.
Il est par exemple possible de jouer sur les paramètres enable_XXXX qui peuvent être positionnées au niveau session, utilisateur, base ou cluster ou encore sur random_page_cost et seq_page_cost.
Il existe par ailleurs des extensions permettant de figer les plans mais je n'en ai pas eu l'usage pour l'instant. Il faut surtout noter que figer les plans peut être dangereux, cela ne garantit PAS des temps de réponse constants. Tout l'intérêt d'un optimiseur basé sur les statistiques est justement de pouvoir s'adapter au contexte.