We have a Postgresql development server on Windows and a production server on Ubuntu Linux. The Postgresql release versions are slightly different, the newer in on linux (Postgresql 14.5).
One database was exported from development and imported in production, so they are the same. We noticed that some queries are 100 times slower on Ubuntu server. Too much to blame a different hardware. The critical queries are like the following:
SELECT a.field1,
...
a.field_n,
CASE (EXISTS (
SELECT b."waterBodyCode"
FROM "GAP_ecologico_SWB_1" b
WHERE a.field1 = b.field1_b AND ...))
WHEN true
THEN 'Yes'
ELSE 'No'
END AS "attributableQE"
FROM "SWB_ecological_quality_elements" a
ORDER BY a.field1, ...;
I know we can rewrite the query in a better and more efficient way but before doing that we would like to understand the root cause of the poor performance on Ubuntu.
SWB_ecological_quality_elements and swaterBodyCode are views. What we suspect is that on Windows server the inner query is executed using indexing or caching while on Ubuntu a full scan is done. The postgresql.conf files are quite similar, what can we check ?
UPDATE 1
I tuned the memory as suggested by PgTune but it was useless, same poor performance. I executed the query with Analyze, the timing result looks weird but I'm not an expert.
" Functions: 1499"
" Options: Inlining true, Optimization true, Expressions true, Deforming true"
" Timing: Generation 101.424 ms, Inlining 103.476 ms, Optimization 10402.780 ms, Emission 6866.288 ms, Total 17473.968 ms"
"Execution Time: 18005.457 ms"
UPDATE 2
Setting jit to false cause an incredible performance boost on the query execute on the Linux server. Don't know anything about jit and cannot figure out why it behaves so differently on Windows (Postgresql 14.4) and on Linux (Postgresql 14.5), but others noticed its weird effects as can be read here: Handling performance problems with jit in postgres 12. Any explanation ?
