pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Strange query behaviour between 9.4 and 12beta1 2+ messages / 2 participants [nested] [flat]
* Strange query behaviour between 9.4 and 12beta1 @ 2019-06-04 14:34 Vasilis Ventirozos <v.ventirozos@gmail.com> 2019-06-04 23:01 ` Re: Strange query behaviour between 9.4 and 12beta1 Tomas Vondra <tomas.vondra@2ndquadrant.com> 0 siblings, 1 reply; 2+ messages in thread From: Vasilis Ventirozos @ 2019-06-04 14:34 UTC (permalink / raw) To: pgsql-performance 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Strange query behaviour between 9.4 and 12beta1 2019-06-04 14:34 Strange query behaviour between 9.4 and 12beta1 Vasilis Ventirozos <v.ventirozos@gmail.com> @ 2019-06-04 23:01 ` Tomas Vondra <tomas.vondra@2ndquadrant.com> 0 siblings, 0 replies; 2+ messages in thread From: Tomas Vondra @ 2019-06-04 23:01 UTC (permalink / raw) To: Vasilis Ventirozos <v.ventirozos@gmail.com>; +Cc: pgsql-performance On Tue, Jun 04, 2019 at 05:34:07PM +0300, Vasilis Ventirozos wrote: >Hello everyone, >I started comparing performance between postgres 9.4 and 12beta1 more specifically comparing the new (materialized) CTE. Are you saying the CTE is specified as MATERIALIZED in the query on 12? Because I don't see it in the explain plan (it's mentioned in the 9.4 plan, though). >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 : > Meh. > >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 > You have two equality conditions for the join. The first one is used to match rows by the hash join itself - it's used to compute the hash value and lookups. But there may be multiple rows that match on either side, generating additional "virtual rows". Those are then removed by the second condition. Consider for example simple cross-join on this table: a | b ------------- 1 | a 1 | b 2 | a 2 | b and the query is SELECT * FROM t t1 JOIN t t2 ON (t1.a = t2.a AND t1.b = t2.b) Now, in the first phase, the hash join might only do (t1.a = t2.a), which will generate 8 rows a | t1.b | t2.b ---------------- 1 | a | a 1 | a | b 1 | b | a 1 | b | b 2 | a | a 2 | a | b 2 | b | a 2 | b | b And then it will apply the second condition (t1.b = t2.b) as a "filter" which will remove some of the rows. In your case the first step generates 4.5B rows the second step discards. I'm not sure why we don't use both conditions as a hash condition. Perhaps it's a data type that does not support hashing, and on 9.4 that does not matter because we end up using merge join. > >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 > regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2019-06-04 23:01 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2019-06-04 14:34 Strange query behaviour between 9.4 and 12beta1 Vasilis Ventirozos <v.ventirozos@gmail.com> 2019-06-04 23:01 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
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