Historique : PostgreSQL utilise un optimiseur basé sur les coûts qui attribue des unités monétaires abstraites aux opérations d'E/S. Les premiers systèmes de base de données ciblaient principalement les disques tournants, où les pénalités de recherche rendaient l'E/S aléatoire environ 40 fois plus coûteuse que les lectures séquentielles. Pour atténuer cette asymétrie, les Bitmap Index Scans ont été introduits pour amortir les récupérations de pages aléatoires en construisant un bitmap en mémoire des emplacements des tuples correspondants et en accédant au tas dans un ordre physique approximatif.
Problème : Le dilemme central survient lors du filtrage de prédicats modérément sélectifs qui correspondent à des milliers de lignes éparpillées sur de nombreuses pages de données. Un Index Scan effectue une E/S aléatoire pour chaque pointeur de tuple correspondant, provoquant un bourdonnement mécanique sur les disques ou des demandes d'E/S excessives sur les SSD. En revanche, un Bitmap Index Scan entraîne des frais généraux pour construire la structure de bitmap et peut traiter des lignes non pertinentes si le bitmap devient défectueux en raison des contraintes de work_mem.
Solution : Le seuil de décision se trouve dans les fonctions cost_index() et cost_bitmap_heap_scan(). Le planificateur estime le nombre de pages de tas distinctes (pages_fetched) nécessaires pour satisfaire la requête. Lorsque pages_fetched dépasse le ratio random_page_cost / seq_page_cost, l'optimiseur privilégie l'approche bitmap car le coût de la récupération de pages triées dépasse la pénalité d'accès aléatoire. La réduction de random_page_cost (par exemple, de 4.0 à 1.1 pour le stockage SSD) abaisse la pénalité perçue de l'E/S aléatoire, poussant le planificateur à revenir vers des Index Scans standard pour des sélectivités qui ont précédemment déclenché la création de bitmaps.
Une plateforme de reporting financier souffrait d'une latence sévère sur une requête de tableau de bord agrégeant les transactions par account_id pour le trimestre fiscal actuel. La table contenait 500 millions de lignes sur un SAN hérité avec des disques tournants. Le prédicat account_id = 12345 correspondait à environ 12 % des lignes éparpillées aléatoirement dans le tas. Le plan d'exécution révélait un Index Scan standard consommant 14 secondes en raison de tempêtes d'E/S aléatoires à travers des milliers de pages de feuilles.
Augmenter random_page_cost de 4.0 à 8.0 a explicitement signalé à l'optimiseur que les recherches aléatoires sur disque étaient prohibitivement coûteuses. Ce changement immédiat a forcé le planificateur à sélectionner un Bitmap Index Scan, réduisant le temps d'exécution à 1.8 secondes en regroupant les demandes de pages dans des plages triées. Cependant, ce paramètre global a pénalisé les requêtes de recherche ponctuelle OLTP ailleurs dans l'application, les contraignant à passer à des scans séquentiels moins efficaces qui augmentaient la contention de verrous pendant les heures de trading de pointe.
Créer un index couvrant sur (account_id, transaction_date, amount) a permis un Index Only Scan qui a contourné entièrement le tas, offrant des temps de réponse de 80 ms. Bien que optimal pour les lectures, l'index composite a fait grossir la taille de la table de 35 % et a diminué le débit d'ingestion de 22 % car chaque insertion nécessitait désormais de maintenir deux grandes structures B-arbre, violant l'engagement strict en matière de SLA pour l'enregistrement des transactions en temps réel.
Nous avons choisi de mettre en œuvre le partitionnement par plage sur created_at combiné à un random_page_cost modéré de 6.0. Cette approche hybride a restreint la requête à la partition du trimestre actuel, réduisant le nombre absolu de pages en dessous du seuil de bitmap, tandis que le paramètre de coût élargi assurait que les requêtes historiques inter-partition utilisaient toujours des bitmaps pour éviter la saturation de l'E/S aléatoire. Cette solution a respecté les contraintes de performance d'écriture du système de trading tout en optimisant le chemin de reporting intensif en lectures.
Résultat : La requête du tableau de bord s'est stabilisée à 400 ms sans dégrader les performances d'insertion OLTP, et l'utilisation de l'E/S disque sur le nœud de reporting est tombée de 95 % à 30 % pendant les heures d'ouverture.
Comment effective_cache_size interagit avec random_page_cost dans le modèle de coût du planificateur, et pourquoi une réduction de random_page_cost sur un système avec un grand cache pourrait-elle dégrader les performances pour certains types de jointures ?
effective_cache_size quantifie la mémoire disponible pour la mise en cache des disques. Lorsqu'il est réglé haut, le planificateur suppose que de nombreuses pages résident dans la RAM, négligeant effectivement les coûts d'E/S quel que soit le réglage de random_page_cost. Si vous réduisez agressivement random_page_cost à 1.1 (typique pour les SSD NVMe) tout en maintenant un effective_cache_size large, l'optimiseur peut irrationnellement privilégier les jointures Nested Loop utilisant des Index Scans plutôt que des Hash Joins. Le modèle suppose que les sondages d'index de la relation interne sont presque gratuits car l'E/S aléatoire est bon marché et mise en cache, ignorant que des boucles internes massives saturent toujours le processeur avec le traitement des tuples et déclenchent l'éviction du cache, conduisant à un pire temps d'horloge que qu'une seule opération de hachage en vrac qui scanne la table interne une fois.
En quoi Bitmap Index Scan de PostgreSQL diffère-t-il d'un Bitmap Heap Scan, et pourquoi le planificateur choisit-il des opérations BitmapOr à travers plusieurs index plutôt que d'utiliser un seul index composite ?
Un Bitmap Index Scan parcourt la structure d'index pour construire un bitmap des pointeurs de tuples correspondants (ou des plages de pages si elles sont défectueuses). Un Bitmap Heap Scan récupère ensuite les données réelles des lignes de la table en utilisant ce bitmap pour accéder aux pages de manière séquentielle. BitmapOr (ou BitmapAnd) se produit lorsque une requête filtre sur des conditions telles que WHERE status = 'active' OR priority = 'high', correspondant à des index séparés. Étant donné que PostgreSQL ne peut pas simultanément parcourir deux B-arbres efficacement en une seule passe, il génère des bitmaps de chaque index indépendamment et les combine avec des opérations bit à bit. Cette technique est privilégiée par rapport à un index composite (status, priority) lorsque des requêtes filtrent sur status seul, priority seul, ou les deux de manière variable, car le maintien de deux index séparés entraîne une amplification beaucoup plus faible des écritures que plusieurs variantes composites couvrantes.
Lorsqu'une requête utilise une clause LIMIT, pourquoi PostgreSQL choisirait-il toujours un Bitmap Index Scan malgré l'arrêt précoce favorisant un Index Scan standard, et comment les statistiques obsolètes influencent-elles cette mauvaise estimation ?
Un Index Scan standard peut s'arrêter immédiatement après la récupération de LIMIT N lignes si l'index supporte l'ordre nécessaire, minimisant l'E/S. Cependant, si le planificateur sous-estime le nombre de lignes satisfaisant le prédicat—en raison de statistiques ANALYZE obsolètes ou de colonnes corrélées—il suppose que l'Index Scan traverserait un nombre excessif de pages de feuille avant de trouver des correspondances. Il sélectionne donc le Bitmap Index Scan pour amortir les coûts d'E/S. Étant donné que les bitmaps doivent être entièrement matérialisés avant l'accès au tas, l'exécuteur ne peut pas interrompre tôt ; il construit un bitmap contenant des milliers de lignes juste pour rejeter toutes sauf les dix premières, entraînant une latence catastrophique par rapport à l'estimation optimiste du planificateur.