Hello everyone,
I have an important finding to share regarding the performance of program indicator calculated queries in PostgreSQL. Over the past two to three weeks, we have observed a significant improvement in query performance, up to ten times better, on PostgreSQL versions prior to version 12. Upon further investigation, we discovered that the Just-In-Time (JIT) compilation feature is enabled in PostgreSQL version 12 and above. While JIT compilation aims to enhance the performance of long-running analytics queries, it can introduce unexpected overhead for queries that are meant to execute faster.
Based on our tests, we found that disabling JIT compilation with the setting “jit=off” resulted in significantly improved execution times for program indicator calculations, leading to enhanced dashboard performance.
We highly recommend incorporating this setting (“jit=off”) in your PostgreSQL configuration file. If you used tools for your setup, you can locate the PostgreSQL configuration file within the PostgreSQL container and apply the setting there. Typically, the file can be found at “/etc/postgresql/<pg_major_version>/main/conf.d/dhispg.conf” if you utilized the tools. Alternatively, you can modify the default “/etc/postgresql/<pg_major_version>/main/postgresql.conf” file.
You may find the official PostgreSQL documentation on when to use JIT helpful: JIT Decision Documentation.
We value your feedback on the performance impact after tuning off JIT (jit=off). Please don’t hesitate to reach out to us with any questions or if you require further assistance.
Best regards,
Tito Kipkurgat