Hello everyone,
I started comparing performance between postgres 9.4 and 12beta1 more specifically comparing the new (materialized) CTE.
The statements i use are application statements that i have little control over,
Hardware is identical as both clusters are running on the same server, on the same disks, with the same data.
Also, cluster settings are almost identical and both clusters have been analyzed.
In all my tests 12 is faster , sometimes much faster, apart from one single query that takes ~12 seconds on 9.4 and nearly 300 seconds on 12.
Plans for both :
The plans are obfuscated , apologies for that but what stands out is the following :
Hash Left Join (cost=200,673.150..203,301.940 rows=153,121 width=64) (actual time=1,485.883..284,536.440 rows=467,123 loops=1) - Hash Cond: (lima_sierra(six_lima_november2.victor_romeo, 1) = foxtrot_hotel.victor_romeo)
- Join Filter: (whiskey_uniform1.six_sierra = foxtrot_hotel.uniform_juliet)
- Rows Removed by Join Filter: 4549925366
I really can't understand what these 4.5bil rows have been removed from, there is nothing suggesting that this dataset was ever created (eg. temp)
and these numbers definitely don't match what i was expecting, which is more or less what i'm seeing in 9.4 plan.
Obviously i've tested this more than once and this behaviour consists.
Best Regards,
Vasilis Ventirozos