Disabling merge and hash join speads up query 25-times

  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:

set enable_mergejoin = off;
set enable_hashjoin = off;
explain (analyze, timing, buffers)

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)

Explain with disabling merge and hash join (execution time 312 ms)

I don’t know if this is some upgrade problem or something… Can someone look at above explains?
Is this PostgreSQL or TimescaleDB problem?

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.

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

Sorry for late reply. I have somehow missed this post.

I assume “catastrophic results” refers to performance and not to data consistency, isn’t it?

I can’t verify now… Is it v15.2 to v15.4 really and upgrade process. Isn’t upgrade the process between different versions e.g. v14.x to v15.x?

Strange minor update would generally not require update of statistics ie 15.2 to 15.4

But proof is in the pudding I guess