Equivalent d'optimizer_index_cost_adj et optimizer_index_caching avec PostgreSQL

[Phil]

Merci au développeur d'un applicatif OLTP passant d'Oracle Database à PostgreSQL pour sa question :

"Bonjour, nous positionnons dans la partie transactionnelle de l'appli les paramètres optimizer_index_cost_adj et optimizer_index_caching à des valeurs différentes des défauts qui sont 100 et 0. On met par exemple 20 et 80. Que faut-il faire avec PostgreSQL ?"

Réponse :
Avec les versions récentes d'Oracle Database (10g+) il n'est plus nécessairement conseillé de positionner ces paramètres. En théorie, calculer les statistiques système est suffisant. En pratique, il peut en effet encore être utile de les positionner.
Les valeurs par défaut, 0 et 100 sont historiquement plutôt adaptées pour du décisionnel. Le 0 d'optimizer_index_caching indique par exemple que l'optimiseur s'attend à ce que 0% des blocs d'index soient en cache pour effectuer des boucles imbriquées (nested loops). Cela l'incite donc indirectement à faire des jointures par hachage (hash join)
De même diminuer la valeur d'index_cost_adj indique à l'optimiseur de diminuer le coût relatif du passage par les index et favorise donc ces passages plutôt que d'effectuer des balayages complets de table (full scan)
Modifier les valeurs dans le sens que vous indiquez est donc plutôt aller dans le sens d'un comportement classique en OLTP : passages par les index, jointures par boucles imbriquées. Toutefois pour des requêtes OLTP très simples, même les valeurs de 0 et 100 amènent des boucles imbriquées et du passage par les index. Avoir absolument besoin de les bouger peut parfois être une indication d'un problème plus profond (...les requêtes sont-elles vraiment de nature OLTP ? les index sont-ils vraiment adaptés ? les statistiques sont-elles à jour ?)

Avec PostgreSQL, c'est random_page_cost et seq_page_cost qu'il faut considérer pour agir sur les mêmes leviers. Par défaut, random_page_cost (I/O aléatoires) est à 4 et seq_page_cost (I/O séquentielles) est à 1. Le planner considère ainsi que les I/O aléatoires (=> index scan) sont 40 fois plus coûteuses que les balayages complets de tables (=> seq scan) MAIS que les blocs sont à 90% en cache d'où un facteur de 1 à 4. C'est assez proche du paramétrage que vous utilisez actuellement en OLTP avec Oracle Database.
De manière générale, mieux vaut laisser le paramétrage par défaut et ne le modifier qu'en cas de besoin en considérant les divers éléments : taille de la RAM et tailles des caches, vitesse du sous-système disque (SSD ou HDD ?), nature des requêtes etc. Avec du SSD, les I/O aléatoires ne sont pas 40 fois plus lentes que les I/O séquentielles par exemple.
Il n'est pas forcément judicieux de modifier ce type de paramétrage au niveau du cluster, il est possible d'agir au niveau d'une base, d'un utilisateur, d'une session...