Disabling merge and hash join speads up query 25-times

TimescaleDB and PostgreSQL
1
  1. On Friday I have optimized a query and after optimization it was executed in 300 ms.
  2. Then Saturday I have upgraded PostgreSQL from v15.2 to v15.4 and TimescaleDB from v2.10.1 to v2.12.1.
  3. Today I have executed the same query and now it takes 8 seconds to execute.
  4. 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?

2

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.

3

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.

4

My experience is that when doing postgres upgrade, analyze is run on all table automatically. Was this not the case for you?