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.
Now I have performed analyze command on all tables in query and first query (that previously executed in 8 s) now has Execution Time: 204 ms. If someone interested, how explain looks like now.
Hi @ts101, thank you very much for sharing all your insights here.
Just be careful as the answer I got from Timescale database engineers was the following:
selection of join algorithm is completely done by postgres and can occasionally have catastrophic results when the estimates are wrong.