- On Friday I have optimized a query and after optimization it was executed in 300 ms.
- Then Saturday I have upgraded PostgreSQL from v15.2 to v15.4 and TimescaleDB from v2.10.1 to v2.12.1.
- Today I have executed the same query and now it takes 8 seconds to execute.
- I remember looking at explain on Friday and the join was performed as “nested loop join”, but today I see in explain there are “merge” and “hash joins” instead.
For this same query I have now tested to disable merge and hash join with:
begin;
set enable_mergejoin = off;
set enable_hashjoin = off;
explain (analyze, timing, buffers)
SELECT...
end
and it is executed in 300 ms (little bit more) and I see “nested loop” join again in explain.
I have copied both explains on explain_depesz_com web site, because it is easier to see complex explain.
Explain with database valid parameters (execution time: 8103 ms)
https://explain.depesz.com/s/q8RW#html
Explain with disabling merge and hash join (execution time 312 ms)
https://explain.depesz.com/s/X7Uhu#html
I don’t know if this is some upgrade problem or something… Can someone look at above explains?
Is this PostgreSQL or TimescaleDB problem?