{"id":1731,"date":"2023-08-22T12:05:23","date_gmt":"2023-08-22T10:05:23","guid":{"rendered":"https:\/\/www.loicmathieu.fr\/wordpress\/?p=1731"},"modified":"2023-08-22T12:18:41","modified_gmt":"2023-08-22T10:18:41","slug":"optimisation-dindex-postgresql","status":"publish","type":"post","link":"https:\/\/www.loicmathieu.fr\/wordpress\/fr\/informatique\/optimisation-dindex-postgresql\/","title":{"rendered":"Optimisation d’index PostgreSQL"},"content":{"rendered":"

Il y a quelque temps, j\u2019avais travaill\u00e9 sur des optimisations de temps d’ex\u00e9cution de requ\u00eates pour PostgreSQL, j’en parle ici : LE PROFILER SQL DE VISUALVM<\/a>.<\/p>\n

Kestra<\/a> est une plate-forme d\u2019orchestration et de scheduling de donn\u00e9e hautement scalable, qui cr\u00e9e, ex\u00e9cute, planifie, et surveille des millions de pipelines complexes. C\u2019est aussi la soci\u00e9t\u00e9 dans laquelle je travaille !<\/p>\n

Kestra, en version open source, utilise un moteur base de donn\u00e9es, l\u2019Edition d\u2019Entreprise permet d\u2019utiliser un moteur alternatif bas\u00e9 sur Kafka et Elasticsearch. Le moteur base de donn\u00e9es supporte H2, PostgreSQL, et MySQL. L\u2019optimisation en question a \u00e9t\u00e9 r\u00e9alis\u00e9 pour PostgreSQL.<\/p>\n

PostgreSQL ne permet pas de forcer l’utilisation d’un index, lors de mes recherches pr\u00e9c\u00e9dentes d’am\u00e9lioration de performance, j’avais remarqu\u00e9 qu’un index tr\u00e8s important sur la table queues<\/code> n’\u00e9tait pas utilis\u00e9, l’index de la primary key \u00e9tait utilis\u00e9 \u00e0 la place. Pour pallier \u00e0 ce probl\u00e8me, j’avais supprim\u00e9 la primary key et remplac\u00e9 par un index de type hash sur la m\u00eame colonne. Mais il s’est av\u00e9r\u00e9 que cette astuce ne marchait pas dans 100% des cas. Parfois, PostgreSQL s’obstinait \u00e0 vouloir utiliser un mauvais index, et h\u00e9las, plus la taille de la table queues<\/code> \u00e9tait important, moins PostgreSQL choisissait le bon index.<\/p>\n

Lors de mes tests sur une table queues<\/code> contenant 3 millions d’enregistrements, PostgreSQL choisissait presque syst\u00e9matiquement le mauvais index amenant \u00e0 des temps d\u2019ex\u00e9cution de plus d’une seconde alors que passer par le bon index ne prend pas plus d’une milliseconde.<\/p>\n

Il me fallait donc comprendre pourquoi PostgreSQL choisissait un autre index. Et pour comprendre, il faut regarder en d\u00e9tail la structure de la table, les requ\u00eates, et le \u00ab\u00a0presque\u00a0\u00bb … les cas o\u00f9 il choisissait le bon index.<\/p>\n

Voici la structure de la table queues<\/code> :<\/p>\n

\n
\n

CREATE<\/span> TABLE<\/span> queues<\/span> (\n \u00ab\u00a0offset\u00a0\u00bb<\/span> serial4<\/span> NOT<\/span> NULL<\/span>,\n \u00ab\u00a0type\u00a0\u00bb<\/span> public<\/span>.\u00ab\u00a0queue_type\u00a0\u00bb<\/span> NOT<\/span> NULL<\/span>,\n \u00ab\u00a0key\u00a0\u00bb<\/span> varchar<\/span>(250<\/span>) NOT<\/span> NULL<\/span>,\n value<\/span> jsonb<\/span> NOT<\/span> NULL<\/span>,\n updated<\/span> timestamptz<\/span> NOT<\/span> NULL<\/span> DEFAULT<\/span> CURRENT_TIMESTAMP<\/span>,\n consumer_indexer<\/span> bool<\/span> NULL<\/span> DEFAULT<\/span> false<\/span>,\n consumer_executor<\/span> bool<\/span> NULL<\/span> DEFAULT<\/span> false<\/span>,\n consumer_worker<\/span> bool<\/span> NULL<\/span> DEFAULT<\/span> false<\/span>,\n consumer_scheduler<\/span> bool<\/span> NULL<\/span> DEFAULT<\/span> false<\/span>,\n consumer_flow_topology<\/span> bool<\/span> NULL<\/span> DEFAULT<\/span> false<\/span>,\n consumer_group<\/span> varchar<\/span>(250<\/span>) NULL<\/span>\n);<\/span><\/p>\n\n<\/div>\n<\/div>\n

Cette table est requ\u00eat\u00e9e chaque seconde par chaque consommateur (indexer, executor, worker, scheduler, flow_topology) pour savoir s’il y a des messages \u00e0 traiter. Il y a un index par consommateur qui devrait permettre \u00e0 ce que ces requ\u00eates soient tr\u00e8s rapides.<\/p>\n

Voici un de ces indexes :<\/p>\n

\n
\n

CREATE<\/span> INDEX<\/span> queues_type__consumer_executor<\/span>\nON<\/span> queues<\/span> USING<\/span> btree<\/span> (type<\/span>, consumer_executor<\/span>);<\/span><\/p>\n\n<\/div>\n<\/div>\n

H\u00e9las, l’index n’est pas syst\u00e9matiquement pris en compte lors du requ\u00eatage. \u00c0 la place, PostgreSQL choisit le plus souvent de faire un scan de l’index queues_offset<\/code> qui est un index ne contenant que la colonne offset. Il privil\u00e9gie donc un seq scan \u00e0 un bitmap index scan. Il existe une astuce pour forcer l’utilisation de l’index queues_type<strong>consumer_executor<\/code>, c’est de configurer PostgreSQL avec un co\u00fbt de page al\u00e9atoire plus faible que le d\u00e9faut de 4 (random_page_cost=1.1 par exemple). Le probl\u00e8me est donc que l’analyser de co\u00fbt de PostgreSQL d\u00e9cide qu’utiliser l’index queues_type<\/strong>consumer_executor<\/code> a un co\u00fbt potentiel plus important que de scanner l’index queues_offset<\/code>. Comme nous ne pouvons demander \u00e0 chacun de nos utilisateurs de configurer PostgreSQL d’une certaine mani\u00e8re, il nous faut trouver une autre solution.<\/p>\n

Regardons maintenant une des requ\u00eates probl\u00e9matiques :<\/p>\n

\n
\n

select<\/span> \u00ab\u00a0value\u00a0\u00bb<\/span>, \u00ab\u00a0offset\u00a0\u00bb<\/span> from<\/span> queues<\/span><\/p>\n

where<\/span> ((type<\/span> = CAST<\/span>(‘io.kestra.core.models.executions.Execution’<\/span> AS<\/span> queue_type<\/span>))<\/p>\n

and<\/span> \u00ab\u00a0consumer_scheduler\u00a0\u00bb<\/span> = false<\/span> and<\/span> \u00ab\u00a0consumer_group\u00a0\u00bb<\/span> is<\/span> null<\/span>)<\/p>\n

order<\/span> by<\/span> \u00ab\u00a0offset\u00a0\u00bb<\/span> asc<\/span> fetch<\/span> next<\/span> 100<\/span> rows<\/span> only<\/span> for<\/span> update<\/span> skip<\/span> locked<\/span><\/p>\n\n<\/div>\n<\/div>\n

Chaque consumer (ici le scheduler) va requ\u00eater un type de messages (ici l’Execution), puis mettre leur flag \u00e0 true pour indiquer que le message a \u00e9t\u00e9 trait\u00e9. On appelle ces requ\u00eates les requ\u00eates de poll.<\/p>\n

\u00c0 premi\u00e8re vue, la requ\u00eate devrait utiliser mon index. Sauf que, apr\u00e8s analyse des statistiques de la table, je me suis rendu compte que les valeurs des donn\u00e9es \u00e9taient mal r\u00e9parties. Et c’est logique ! Certain consumer ont tr\u00e8s peu de messages, donc leur flag est \u00e0 false dans plus de 95% des lignes rendant l’index peu discriminant. Certains consomment quasiment 50% de la table, de la m\u00eame mani\u00e8re le flag sera \u00e0 50% \u00e0 la valeur true rendant l’index trop co\u00fbteux.<\/p>\n

Regardons en d\u00e9tail l’exemple ci-dessus : 60% des lignes sont de type io.kestra.core.models.executions.Execution<\/code> et 50% des lignes ont le flag consumer_scheduler<\/code> \u00e0 false, ce qui nous donne donc une probabilit\u00e9 d’avoir les deux colonnes \u00e0 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.<\/p>\n

De plus, m\u00eame si on ne requ\u00eate pas sur la colonne offset, elle fait partie de la clause order by. C’est sans doute pour cela que comme l’index \u00e9tant peu discriminant, PostgreSQL lui pr\u00e9f\u00e8re l’index sur la colonne offset.<\/p>\n

En ajoutant la colonne offset \u00e0 l’index queues_type__consumer_executor<\/code>, PostgreSQL va alors pr\u00e9f\u00e9rer cet index, car la colonne lui sera utile pour faire son order by, c’est un peu comme si je lui avais forc\u00e9 la main.<\/p>\n

Ce qui donne l’index suivant qui est maintenant utilis\u00e9 pour ma requ\u00eate :<\/p>\n

CREATE<\/span> INDEX<\/span> queues_type__consumer_executor<\/span>\nON<\/span> queues<\/span> USING<\/span> btree<\/span> (type<\/span>, consumer_executor, \u00ab\u00a0offset\u00a0\u00bb<\/span><\/span>);<\/span><\/p>\n

Mais on peut encore faire mieux ! Comme les requ\u00eates sont toujours faites avec un flag \u00e0 false, je peux rendre les indexes partiels pour \u00e9conomiser de l’espace disque, car une fois le message trait\u00e9, je ne vais plus jamais y acc\u00e9der.<\/p>\n

Ce qui donne l’index suivant :<\/p>\n

\n
\n

CREATE<\/span> INDEX<\/span> queues_type__consumer_executor<\/span>\nON<\/span> queues<\/span> USING<\/span> btree<\/span> (type<\/span>, consumer_executor<\/span>, \u00ab\u00a0offset\u00a0\u00bb<\/span>) WHERE<\/span> (consumer_executor<\/span> = false<\/span>);<\/span><\/p>\n

<\/p>\n\n<\/div>\n<\/div>\n

Avec ce nouvel index, le temps moyen d’une requ\u00eate de poll sur la table queues est pass\u00e9 de 1s \u00e0 1ms !<\/p>\n

PostgreSQL est une super base de donn\u00e9es, le fait de ne pouvoir forcer un index nous force \u00e0 nous poser des questions :\u00a0pourquoi mon index n’est-il pas utilis\u00e9 ?, quelle est la distribution des donn\u00e9es dans ma table ?, … Ce sont de bonnes questions, qui nous permettent de mieux comprendre notre utilisation de la base de donn\u00e9es, et d’am\u00e9liorer les performances de nos requ\u00eates en fournissant \u00e0 PostgreSQL le bon index et pas juste l’index que nous pensions \u00eatre le bon.<\/p>\n

Si vous aussi, vous voulez analyser les statistiques d’une table, voici la requ\u00eate que j’ai utilis\u00e9e :<\/p>\n

\n
\n

SELECT<\/span> tablename<\/span>, schemaname<\/span>, attname<\/span> As<\/span> colname<\/span>, n_distinct<\/span>,<\/p>\n

array_to_string<\/span>(most_common_vals<\/span>, E’\\n’<\/span>) AS<\/span> common_vals<\/span>,<\/p>\n

array_to_string<\/span>(most_common_freqs<\/span>, E’\\n’<\/span>) As<\/span> dist_freq<\/span><\/p>\n

FROM<\/span> pg_stats<\/span><\/p>\n

WHERE<\/span> tablename<\/span> = ‘queues’<\/span><\/p>\n

ORDER<\/span> BY<\/span> schemaname<\/span>, tablename<\/span>, attname<\/span>\n;<\/span><\/p>\n

<\/p>\n\n<\/div>\n<\/div>\n

<\/p>","protected":false},"excerpt":{"rendered":"

Il y a quelque temps, j\u2019avais travaill\u00e9 sur des optimisations de temps d’ex\u00e9cution de requ\u00eates pour PostgreSQL, j’en parle ici : LE PROFILER SQL DE VISUALVM. Kestra est une plate-forme d\u2019orchestration et de scheduling de donn\u00e9e hautement scalable, qui cr\u00e9e, ex\u00e9cute, planifie, et surveille des millions de pipelines complexes. C\u2019est aussi la soci\u00e9t\u00e9 dans laquelle je travaille ! Kestra, en version open source, utilise un moteur base de donn\u00e9es, l\u2019Edition d\u2019Entreprise permet d\u2019utiliser un moteur alternatif bas\u00e9 sur Kafka et…

Lire la suite Lire la suite<\/span><\/a><\/p><\/p>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"activitypub_content_warning":"","activitypub_content_visibility":"","activitypub_max_image_attachments":4,"activitypub_interaction_policy_quote":"anyone","activitypub_status":"","footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[9],"tags":[203,219,159,213,220],"class_list":["post-1731","post","type-post","status-publish","format-standard","hentry","category-informatique","tag-database","tag-index","tag-performance","tag-postgres","tag-postgresql"],"aioseo_notices":[],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_likes_enabled":true,"jetpack-related-posts":[{"id":1650,"url":"https:\/\/www.loicmathieu.fr\/wordpress\/fr\/informatique\/le-profiler-sql-de-visualvm\/","url_meta":{"origin":1731,"position":0},"title":"Le profiler SQL de VisualVM","author":"admin","date":"mardi 4 avril 2023","format":false,"excerpt":"Il y a peu, j'ai d\u00e9couvert le profiler SQL de VisualVM et je me suis dit que je devais aussi vous le faire d\u00e9couvrir ;). VisualVM est un outil qui fournit une interface visuelle pour afficher des informations d\u00e9taill\u00e9es sur les applications qui s'ex\u00e9cutent sur une machine virtuelle Java (JVM).\u2026","rel":"","context":"Dans "informatique"","block_context":{"text":"informatique","link":"https:\/\/www.loicmathieu.fr\/wordpress\/fr\/category\/informatique\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/loicmathieu.fr\/wordpress\/wp-content\/uploads\/Capture-decran-du-2023-04-03-14-17-25-1024x624.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/loicmathieu.fr\/wordpress\/wp-content\/uploads\/Capture-decran-du-2023-04-03-14-17-25-1024x624.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/loicmathieu.fr\/wordpress\/wp-content\/uploads\/Capture-decran-du-2023-04-03-14-17-25-1024x624.png?resize=525%2C300&ssl=1 1.5x"},"classes":[]},{"id":2007,"url":"https:\/\/www.loicmathieu.fr\/wordpress\/fr\/informatique\/characterescapes-la-perle-cachee-de-jackson\/","url_meta":{"origin":1731,"position":1},"title":"CharacterEscapes: la perle cach\u00e9e de Jackson","author":"admin","date":"mercredi 10 septembre 2025","format":false,"excerpt":"A Kestra, la plateforme d'orchestration de donn\u00e9es pour laquelle je travaille, on a eu une issue (#10326) ouverte d'un utilisateur qui rapportait un probl\u00e8me avec la base PostgreSQL et le caract\u00e8re Unicode \\u0000. Une t\u00e2che de workflow qui retournait ce caract\u00e8re en outpout plantait. Apr\u00e8s investigation, PostgreSQL refuse de stocker\u2026","rel":"","context":"Dans "informatique"","block_context":{"text":"informatique","link":"https:\/\/www.loicmathieu.fr\/wordpress\/fr\/category\/informatique\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1786,"url":"https:\/\/www.loicmathieu.fr\/wordpress\/fr\/informatique\/concevoir-un-saas-multitenant\/","url_meta":{"origin":1731,"position":2},"title":"Concevoir un SaaS multitenant","author":"admin","date":"mardi 5 mars 2024","format":false,"excerpt":"Cet article se repose sur mon talk Concevoir un SaaS multitenant fait \u00e0 Cloud Nord le 12 octobre 2023. Kestra est une plate-forme d\u2019orchestration et de scheduling de donn\u00e9e hautement scalabe, qui cr\u00e9e, ex\u00e9cute, planifie et surveille des millions de pipelines complexes. Pour une introduction \u00e0 Kestra, vous pouvez lire\u2026","rel":"","context":"Dans "informatique"","block_context":{"text":"informatique","link":"https:\/\/www.loicmathieu.fr\/wordpress\/fr\/category\/informatique\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/loicmathieu.fr\/wordpress\/wp-content\/uploads\/kestra-software-architecture-1024x576.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/loicmathieu.fr\/wordpress\/wp-content\/uploads\/kestra-software-architecture-1024x576.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/loicmathieu.fr\/wordpress\/wp-content\/uploads\/kestra-software-architecture-1024x576.png?resize=525%2C300&ssl=1 1.5x"},"classes":[]},{"id":1611,"url":"https:\/\/www.loicmathieu.fr\/wordpress\/fr\/informatique\/introduction-a-kestra\/","url_meta":{"origin":1731,"position":3},"title":"Introduction \u00e0 Kestra","author":"admin","date":"lundi 6 mars 2023","format":false,"excerpt":"Kestra est un orchestrateur et scheduler de donn\u00e9e open source. Avec Kestra, les workflows de donn\u00e9es, appel\u00e9s flows, utilisent le format YAML et sont ex\u00e9cut\u00e9s par son moteur via un appel API, l'interface utilisateur, ou un trigger (webhook, schedule, SQL query, Pub\/Sub message, \u2026). Les notions importantes de Kestra sont\u2026","rel":"","context":"Dans "informatique"","block_context":{"text":"informatique","link":"https:\/\/www.loicmathieu.fr\/wordpress\/fr\/category\/informatique\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/loicmathieu.fr\/wordpress\/wp-content\/uploads\/kestra-01-1024x267.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/loicmathieu.fr\/wordpress\/wp-content\/uploads\/kestra-01-1024x267.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/loicmathieu.fr\/wordpress\/wp-content\/uploads\/kestra-01-1024x267.png?resize=525%2C300&ssl=1 1.5x"},"classes":[]},{"id":1674,"url":"https:\/\/www.loicmathieu.fr\/wordpress\/fr\/informatique\/devoxx-fr-2023-foundation-db-le-secret-le-mieux-garde-des-nouvelles-architectures-distribuees-par-pierre-zemb-et-steven-le-roux\/","url_meta":{"origin":1731,"position":4},"title":"Devoxx FR 2023 – FoundationDB : le secret le mieux gard\u00e9 des nouvelles architectures distribu\u00e9es ! par Pierre Zemb et Steven Le Roux","author":"admin","date":"lundi 17 avril 2023","format":false,"excerpt":"Il existe pr\u00e8s de 900 BDD (cf https:\/\/dbdb.io\/) et chacune a ses particularit\u00e9s : mod\u00e8le de requ\u00eatage, mod\u00e9lisation de la donn\u00e9e, moteur de stockage, .... Existe-t-il une de ces trois caract\u00e9ristiques que l'on peut mutualiser ? Oui, toutes les bases de donn\u00e9es stockent de la donn\u00e9e, on doit donc pouvoir\u2026","rel":"","context":"Dans "informatique"","block_context":{"text":"informatique","link":"https:\/\/www.loicmathieu.fr\/wordpress\/fr\/category\/informatique\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1606,"url":"https:\/\/www.loicmathieu.fr\/wordpress\/fr\/informatique\/au-revoir-zenika-bonjour-kestra\/","url_meta":{"origin":1731,"position":5},"title":"Au revoir Zenika, bonjour Kestra","author":"admin","date":"mardi 10 janvier 2023","format":false,"excerpt":"Apr\u00e8s 4 ans, 4 mois et 4 jours (ou presque), je quitte Zenika. Ces 4 ann\u00e9es et quelques auront \u00e9t\u00e9 parmi les plus passionnantes de ma vie professionnelle. Je suis triste de quitter cette super soci\u00e9t\u00e9 dans laquelle j'ai pu m'\u00e9panouir, grandir et apprendre plein de choses. Je suis s\u00fbr\u2026","rel":"","context":"Dans "informatique"","block_context":{"text":"informatique","link":"https:\/\/www.loicmathieu.fr\/wordpress\/fr\/category\/informatique\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"https:\/\/www.loicmathieu.fr\/wordpress\/fr\/wp-json\/wp\/v2\/posts\/1731","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.loicmathieu.fr\/wordpress\/fr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.loicmathieu.fr\/wordpress\/fr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.loicmathieu.fr\/wordpress\/fr\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.loicmathieu.fr\/wordpress\/fr\/wp-json\/wp\/v2\/comments?post=1731"}],"version-history":[{"count":14,"href":"https:\/\/www.loicmathieu.fr\/wordpress\/fr\/wp-json\/wp\/v2\/posts\/1731\/revisions"}],"predecessor-version":[{"id":1746,"href":"https:\/\/www.loicmathieu.fr\/wordpress\/fr\/wp-json\/wp\/v2\/posts\/1731\/revisions\/1746"}],"wp:attachment":[{"href":"https:\/\/www.loicmathieu.fr\/wordpress\/fr\/wp-json\/wp\/v2\/media?parent=1731"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.loicmathieu.fr\/wordpress\/fr\/wp-json\/wp\/v2\/categories?post=1731"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.loicmathieu.fr\/wordpress\/fr\/wp-json\/wp\/v2\/tags?post=1731"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}