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
http://www.mindtree.com/email/disclaimer.html