Performance issue post upgrade on Version 13 - Incorrect Estimation Cost choosing Hash Aggregate-Nested Left Loop Join

Hello Team,

There is change in query plan in 12.4 version and Version 13 resulting in performance slowness post upgrade.

* In 12.4 version, Sort Operation Group Aggregate is selected which results to Merge Join. Query takes ~5 seconds.
* In 13.5 version, optimizer wrongly estimates and due to new Disk Based Hash Aggregate feature, it prefers Hash Aggregate instead of Sort Operation which finally blocks merge-join and chooses Nested Loop Left Join. Query takes ~5 minutes.

When we increase work_mem to 23 MB, Disk Usage gets cleared from Query Plan but still Optimizer estimates Hash Aggregate-Nested Loop Left Join (compared to Sort-Merge Join) causing slowness. Query takes ~22 seconds.

Version 13 query plan has lower estimated cost than that of 12.4 which implies 13.5 planner thought it found a better plan, but it is running slower.

12.4 Version:
"Merge Right Join (cost=202198.78..295729.10 rows=1 width=8) (actual time=1399.727..5224.574 rows=296 loops=1)"

13.5 version:-
"Nested Loop Left Join (cost=196360.90..287890.45 rows=1 width=8) (actual time=3209.577..371300.693 rows=296 loops=1)"

Thanks & Regards,

Prajna Shetty
Technical Specialist,
Data Platform Support & Delivery
[cid:image001(dot)png(at)01D83D43(dot)B39FD810]

________________________________

http://www.mindtree.com/email/disclaimer.html