From: Vasilis Ventirozos <v.ventirozos@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Strange query behaviour between 9.4 and 12beta1
Date: Tue, 4 Jun 2019 17:34:07 +0300
Message-ID: <32F9F8AD-2BBE-48D7-9A05-F33D3A90A9FA@gmail.com> (raw)
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 :
Plan for 12 <https://explain.depesz.com/s/wRXO;
plan for 9.4 <https://explain.depesz.com/s/njtH;
The plans are obfuscated , apologies for that but what stands out is the following :
Hash Left Join <http://www.depesz.com/2013/05/09/explaining-the-unexplainable-part-3/#join-modifiers; (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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: v.ventirozos@gmail.com
Subject: Re: Strange query behaviour between 9.4 and 12beta1
In-Reply-To: <32F9F8AD-2BBE-48D7-9A05-F33D3A90A9FA@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox