Il y a quelque temps, j’avais travaillé sur des optimisations de temps d’exécution de requêtes pour PostgreSQL, j’en parle ici : LE PROFILER SQL DE VISUALVM.

Kestra est une plate-forme d’orchestration et de scheduling de donnée hautement scalable, qui crée, exécute, planifie, et surveille des millions de pipelines complexes. C’est aussi la société dans laquelle je travaille !

Kestra, en version open source, utilise un moteur base de données, l’Edition d’Entreprise permet d’utiliser un moteur alternatif basé sur Kafka et Elasticsearch. Le moteur base de données supporte H2, PostgreSQL, et MySQL. L’optimisation en question a été réalisé pour PostgreSQL.

PostgreSQL ne permet pas de forcer l’utilisation d’un index, lors de mes recherches précédentes d’amélioration de performance, j’avais remarqué qu’un index très important sur la table queues n’était pas utilisé, l’index de la primary key était utilisé à la place. Pour pallier à ce problème, j’avais supprimé la primary key et remplacé par un index de type hash sur la même colonne. Mais il s’est avéré que cette astuce ne marchait pas dans 100% des cas. Parfois, PostgreSQL s’obstinait à vouloir utiliser un mauvais index, et hélas, plus la taille de la table queues était important, moins PostgreSQL choisissait le bon index.

Lors de mes tests sur une table queues contenant 3 millions d’enregistrements, PostgreSQL choisissait presque systématiquement le mauvais index amenant à des temps d’exécution de plus d’une seconde alors que passer par le bon index ne prend pas plus d’une milliseconde.

Il me fallait donc comprendre pourquoi PostgreSQL choisissait un autre index. Et pour comprendre, il faut regarder en détail la structure de la table, les requêtes, et le « presque » … les cas où il choisissait le bon index.

Voici la structure de la table queues :

Cette table est requêtée chaque seconde par chaque consommateur (indexer, executor, worker, scheduler, flow_topology) pour savoir s’il y a des messages à traiter. Il y a un index par consommateur qui devrait permettre à ce que ces requêtes soient très rapides.

Voici un de ces indexes :

Hélas, l’index n’est pas systématiquement pris en compte lors du requêtage. À la place, PostgreSQL choisit le plus souvent de faire un scan de l’index queues_offset qui est un index ne contenant que la colonne offset. Il privilégie donc un seq scan à un bitmap index scan. Il existe une astuce pour forcer l’utilisation de l’index queues_type__consumer_executor, c’est de configurer PostgreSQL avec un coût de page aléatoire plus faible que le défaut de 4 (random_page_cost=1.1 par exemple). Le problème est donc que l’analyser de coût de PostgreSQL décide qu’utiliser l’index queues_type__consumer_executor a un coût potentiel plus important que de scanner l’index queues_offset. Comme nous ne pouvons demander à chacun de nos utilisateurs de configurer PostgreSQL d’une certaine manière, il nous faut trouver une autre solution.

Regardons maintenant une des requêtes problématiques :

Chaque consumer (ici le scheduler) va requêter un type de messages (ici l’Execution), puis mettre leur flag à true pour indiquer que le message a été traité. On appelle ces requêtes les requêtes de poll.

À première vue, la requête devrait utiliser mon index. Sauf que, après analyse des statistiques de la table, je me suis rendu compte que les valeurs des données étaient mal réparties. Et c’est logique ! Certain consumer ont très peu de messages, donc leur flag est à false dans plus de 95% des lignes rendant l’index peu discriminant. Certains consomment quasiment 50% de la table, de la même manière le flag sera à 50% à la valeur true rendant l’index trop coûteux.

Regardons en détail l’exemple ci-dessus : 60% des lignes sont de type io.kestra.core.models.executions.Execution et 50% des lignes ont le flag consumer_scheduler à false, ce qui nous donne donc une probabilité d’avoir les deux colonnes à la valeur voulue de 30% ce qui n’est pas assez discriminant pour utiliser l’index. Ce que l’optimiser ne peut savoir, c’est qu’en fait il n’y a que quelques pourcents des lignes qui ont ces deux colonnes aux valeurs voulues.

De plus, même si on ne requête pas sur la colonne offset, elle fait partie de la clause order by. C’est sans doute pour cela que comme l’index étant peu discriminant, PostgreSQL lui préfère l’index sur la colonne offset.

En ajoutant la colonne offset à l’index queues_type__consumer_executor, PostgreSQL va alors préférer cet index, car la colonne lui sera utile pour faire son order by, c’est un peu comme si je lui avais forcé la main.

Ce qui donne l’index suivant qui est maintenant utilisé pour ma requête :

CREATE INDEX queues_type__consumer_executor
ON queues USING btree (type, consumer_executor, « offset »);

Mais on peut encore faire mieux ! Comme les requêtes sont toujours faites avec un flag à false, je peux rendre les indexes partiels pour économiser de l’espace disque, car une fois le message traité, je ne vais plus jamais y accéder.

Ce qui donne l’index suivant :

Avec ce nouvel index, le temps moyen d’une requête de poll sur la table queues est passé de 1s à 1ms !

PostgreSQL est une super base de données, le fait de ne pouvoir forcer un index nous force à nous poser des questions : pourquoi mon index n’est-il pas utilisé ?, quelle est la distribution des données dans ma table ?, … Ce sont de bonnes questions, qui nous permettent de mieux comprendre notre utilisation de la base de données, et d’améliorer les performances de nos requêtes en fournissant à PostgreSQL le bon index et pas juste l’index que nous pensions être le bon.

Si vous aussi, vous voulez analyser les statistiques d’une table, voici la requête que j’ai utilisée :