{"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\/informatique\/optimisation-dindex-postgresql\/","title":{"rendered":"PostgreSQL index optimization"},"content":{"rendered":"

Some time ago, I worked on query execution time optimizations for PostgreSQL, I talk about it here: The VISUALVM SQL PROFILE<\/a>.<\/p>\n

Kestra<\/a> is a highly scalable data orchestration and scheduling platform that creates, executes, schedules, and monitors millions of complex pipelines. It’s also the company I work for!<\/p>\n

The open source version of Kestra uses a database engine, while the Enterprise Edition lets you use an alternative engine based on Kafka and Elasticsearch. The database engine supports H2, PostgreSQL and MySQL. The optimization in question was done for PostgreSQL.<\/p>\n

PostgreSQL doesn’t allow you to force the use of an index. During my previous research into improving performance, I noticed that a very important index on the queues<\/code> table was not being used, the primary key index was being used instead. To overcome this problem, I removed the primary key and replaced it with a hash index on the same column. But it turned out that this trick didn’t work in 100% of cases. Sometimes, PostgreSQL would insist on using the wrong index, and alas, the larger the size of the queues<\/code> table, the less PostgreSQL would choose the right index.<\/p>\n

During my tests on a queues table containing 3 million records, PostgreSQL almost systematically chose the wrong index, leading to execution times of over a second, whereas using the right index takes no more than a millisecond.<\/p>\n

So I had to understand why PostgreSQL chose a different index. And to understand that, you have to look in detail at the table structure, the queries, and the “almost” … the cases where it chose the right index.<\/p>\n

Here is the structure of the queues<\/code> table:<\/p>\n

\n
\n

CREATE<\/span> TABLE<\/span> queues<\/span> (\n “offset”<\/span> serial4<\/span> NOT<\/span> NULL<\/span>,\n “type”<\/span> public<\/span>.“queue_type”<\/span> NOT<\/span> NULL<\/span>,\n “key”<\/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

This table is queried every second by each consumer (indexer, executor, worker, scheduler, flow_topology) to find out if there are any messages to process. There is one index per consumer, which should ensure that these queries are very fast.<\/p>\n

Here is one such index:<\/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

Unfortunately, the index is not systematically used when querying. Instead, PostgreSQL most often chooses to scan the queues_offset<\/code> index, which is an index containing only the offset column. It therefore prefers a seq scan to a bitmap index scan. One trick to force the use of the queues_type<strong>consumer_executor<\/code>, index is to configure PostgreSQL with a random page cost lower than the default of 4 (random_page_cost=1.1 for example). The problem, is that PostgreSQL’s cost analyzer decides that using the queues_type<\/strong>consumer_executor<\/code> index has a greater potential cost than scanning the queues_offset<\/code> index. Since we can’t ask each of our users to configure PostgreSQL in a certain way, we need to find another solution.<\/p>\n

Let’s take a look at one of the problematic queries:<\/p>\n

\n
\n

select<\/span> “value”<\/span>, “offset”<\/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>))\nand<\/span> “consumer_scheduler”<\/span> = false<\/span> and<\/span> “consumer_group”<\/span> is<\/span> null<\/span>)<\/p>\n

order<\/span> by<\/span> “offset”<\/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

Each consumer (in this case, the scheduler) will request a message type (in this case, Execution), then set their flag to true to indicate that the message has been processed. These requests are called poll requests.<\/p>\n

At first look, the request should use my index. Except that, after analyzing the table statistics, I realized that the data values were poorly distributed. And that’s logical! Some consumers have very few messages, so their flag is set to false in over 95% of rows, making the index indiscriminate. Some consume almost 50% of the table, so the flag will be set to 50% true, making the index too costly.<\/p>\n

Let’s take a closer look at the example above: 60% of rows are of type io.kestra.core.models.executions.Execution<\/code> and 50% of rows have the consumer_scheduler<\/code> flag set to false, giving us a probability of having both columns at the desired value of 30%, which is not discriminating enough to use the index. What the optimizer can’t know is that, in fact, only a few percent of rows have both columns at the desired values.<\/p>\n

What’s more, even if you don’t query the offset column, it’s part of the order by clause. This is certainly why PostgreSQL prefers the index on the offset column, as the index is not very discriminating.<\/p>\n

By adding the offset column to the queues_type__consumer_executor<\/code> index, PostgreSQL will then prefer this index, as the column will be useful for making its order by, it’s a bit like forcing it to use the index.<\/p>\n

This results in the following index, which is now used for my query:<\/p>\n

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

But we can do even better! Since queries are always made with a flag set to false, I can make indexes partial to save disk space, because once the message has been processed, it will never be accessed again.<\/p>\n

This gives the following index:<\/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>, “offset”<\/span>) WHERE<\/span> (consumer_executor<\/span> = false<\/span>);<\/span><\/p>\n

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

With this new index, the average time for a poll query on the queues<\/code> table has dropped from 1s to 1ms!<\/p>\n

PostgreSQL is a great database, and the fact that we can’t force an index forces us to ask ourselves questions such as: why isn’t my index being used, what is the distribution of data in my table, etc? These are good questions, which enable us to better understand our use of the database, and improve the performance of our queries by providing PostgreSQL with the right index, not just the index we thought was the right one.<\/p>\n

If you too would like to analyze table statistics, here’s the query I used:<\/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":"

Some time ago, I worked on query execution time optimizations for PostgreSQL, I talk about it here: The VISUALVM SQL PROFILE. Kestra is a highly scalable data orchestration and scheduling platform that creates, executes, schedules, and monitors millions of complex pipelines. It’s also the company I work for! The open source version of Kestra uses a database engine, while the Enterprise Edition lets you use an alternative engine based on Kafka and Elasticsearch. The database engine supports H2, PostgreSQL and…

Read More Read More<\/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\/informatique\/le-profiler-sql-de-visualvm\/","url_meta":{"origin":1731,"position":0},"title":"VisualVM SQL profiler SQL","author":"admin","date":"Tuesday April 4th, 2023","format":false,"excerpt":"A little while ago, I discovered the SQL profiler of VisualVM and I thought I should share it with you ;). VisualVM is a tool that provides a visual interface to display detailed information about applications running on a Java Virtual Machine (JVM). VisualVM is designed for use in development\u2026","rel":"","context":"In "informatique"","block_context":{"text":"informatique","link":"https:\/\/www.loicmathieu.fr\/wordpress\/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\/informatique\/characterescapes-jackson-s-hidden-gem\/","url_meta":{"origin":1731,"position":1},"title":"CharacterEscapes: Jackson’s hidden gem","author":"admin","date":"Wednesday September 10th, 2025","format":false,"excerpt":"At Kestra, the data orchestration platform I work for, we had an issue ([#10326] (https:\/\/github.com\/kestra-io\/kestra\/issues\/10326)) opened by a user reporting a problem with the PostgreSQL database and the Unicode character \\u0000. A workflow task that returned this character in its output was failing. After investigation, PostgreSQL refuses to store a\u2026","rel":"","context":"In "informatique"","block_context":{"text":"informatique","link":"https:\/\/www.loicmathieu.fr\/wordpress\/category\/informatique\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1786,"url":"https:\/\/www.loicmathieu.fr\/wordpress\/informatique\/concevoir-un-saas-multitenant\/","url_meta":{"origin":1731,"position":2},"title":"Designing a multi-tenant SaaS","author":"admin","date":"Tuesday March 5th, 2024","format":false,"excerpt":"This article is based on my talk Designing a multi-tenant SaaS given at Cloud Nord on October 12, 2023. Kestra is a highly scalable data scheduling and orchestration platform that creates, executes, schedules and monitors millions of complex pipelines. For an introduction to Kestra, you can read my article on\u2026","rel":"","context":"In "informatique"","block_context":{"text":"informatique","link":"https:\/\/www.loicmathieu.fr\/wordpress\/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\/informatique\/introduction-a-kestra\/","url_meta":{"origin":1731,"position":3},"title":"Introduction to Kestra","author":"admin","date":"Monday March 6th, 2023","format":false,"excerpt":"Kestra is an open-source data orchestrator and scheduler. With Kestra, data workflows, called flows, use the YAML format and are executed by its engine via an API call, the user interface, or a trigger (webhook, schedule, SQL query, Pub\/Sub message, ...). The important notions of Kestra are : The flow:\u2026","rel":"","context":"In "informatique"","block_context":{"text":"informatique","link":"https:\/\/www.loicmathieu.fr\/wordpress\/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\/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":"(Fran\u00e7ais) 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":"Monday April 17th, 2023","format":false,"excerpt":"Sorry, this entry is only available in Fran\u00e7ais.","rel":"","context":"In "informatique"","block_context":{"text":"informatique","link":"https:\/\/www.loicmathieu.fr\/wordpress\/category\/informatique\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1606,"url":"https:\/\/www.loicmathieu.fr\/wordpress\/informatique\/au-revoir-zenika-bonjour-kestra\/","url_meta":{"origin":1731,"position":5},"title":"(Fran\u00e7ais) Au revoir Zenika, bonjour Kestra","author":"admin","date":"Tuesday January 10th, 2023","format":false,"excerpt":"Sorry, this entry is only available in Fran\u00e7ais.","rel":"","context":"In "informatique"","block_context":{"text":"informatique","link":"https:\/\/www.loicmathieu.fr\/wordpress\/category\/informatique\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"https:\/\/www.loicmathieu.fr\/wordpress\/wp-json\/wp\/v2\/posts\/1731","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.loicmathieu.fr\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.loicmathieu.fr\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.loicmathieu.fr\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.loicmathieu.fr\/wordpress\/wp-json\/wp\/v2\/comments?post=1731"}],"version-history":[{"count":14,"href":"https:\/\/www.loicmathieu.fr\/wordpress\/wp-json\/wp\/v2\/posts\/1731\/revisions"}],"predecessor-version":[{"id":1746,"href":"https:\/\/www.loicmathieu.fr\/wordpress\/wp-json\/wp\/v2\/posts\/1731\/revisions\/1746"}],"wp:attachment":[{"href":"https:\/\/www.loicmathieu.fr\/wordpress\/wp-json\/wp\/v2\/media?parent=1731"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.loicmathieu.fr\/wordpress\/wp-json\/wp\/v2\/categories?post=1731"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.loicmathieu.fr\/wordpress\/wp-json\/wp\/v2\/tags?post=1731"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}