Query performance deteriorates significantly after upgrade 19.1

I have an SQL query on Dremio 15 that only takes 1 minute, and 5.30 minutes after upgrading to 19.1. After my analysis, I found that the number of threads displayed in the execution plan of the new version is 1/3 less than that of the old version, and the new version mainly uses virtual memory, even if I close swap, But the old version uses more physical memory. Who knows how to add threads and bind physical memory?

Any one can help me ?

Are you sure the the dremio-env in the upgrade is the same configuration as the old one?

Yes,I had use the same dremio-env and the machine same too.@spireite

Can you share query profiles from the 15 and 19.1 queries?

dremio15_1.zip (34.9 KB)
dremio15_3.zip (2.3 MB)
dremio15_4.zip (2.3 MB)
drmeio15_2.zip (582.8 KB)
dremio191_1.zip (19.2 KB)
dremio191_2.zip (668.0 KB)
dremio191_3.zip (2.2 MB)
drmeio191_4.zip (1.9 MB)


I have to say that the lack of feedback/very slow response from Dremio

Hi @alex.shi Apologies for the delay, we will review the profiles ASAP and get back to you by tomorrow


Q1 - 15.x took 0.967s while 19.x took 1.07s, negligible difference
Q2 - 15.x took 15.29s while 19.x took 13.9s so 19.x is faster
Q3 - 15.x took 55.53s while 19.x took 5m, this is because the scan TABLE_FUNCTION 15-05 had an invalid row count of 1.0 and hence the SCAN was under parallelized (4 threads in 19.x vs 885 threads in 15.x), this should have been addressed in 19.1, would you be able to refresh metadata and try again?


Q4 - same as Q3, same table, so refresh metadata on hivecdc_ha.odsuf.entrust and retry

Unfortunately, due to the failure to find the cause of the problem for a long time, we temporarily gave up the trial of dremio19, and there is no environment to verify this problem.