Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hYAVz-0007Oe-MA for pgsql-performance@arkaria.postgresql.org; Tue, 04 Jun 2019 14:34:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hYAVy-0007PC-84 for pgsql-performance@arkaria.postgresql.org; Tue, 04 Jun 2019 14:34:14 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hYAVx-0007P4-V6 for pgsql-performance@lists.postgresql.org; Tue, 04 Jun 2019 14:34:13 +0000 Received: from gauss.credativ.com ([93.94.130.89]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hYAVv-0003Ig-Av for pgsql-performance@postgresql.org; Tue, 04 Jun 2019 14:34:13 +0000 Received: from gauss.credativ.com (localhost [127.0.0.1]) by gauss.credativ.com (Postfix) with ESMTP id B47D51E1ED9; Tue, 4 Jun 2019 16:34:09 +0200 (CEST) Received: from [192.168.1.6] (free34.static.otenet.gr [79.129.36.85]) (using TLSv1 with cipher ECDHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) (Authenticated sender: vve@gauss.credativ.com) by gauss.credativ.com (Postfix) with ESMTPSA id 6A2C51E1ECC for ; Tue, 4 Jun 2019 16:34:09 +0200 (CEST) From: Vasilis Ventirozos Content-Type: multipart/alternative; boundary="Apple-Mail=_425C4E82-D30B-4885-B8C4-023982CC7F92" Mime-Version: 1.0 (Mac OS X Mail 12.4 \(3445.104.11\)) Subject: Strange query behaviour between 9.4 and 12beta1 Message-Id: <32F9F8AD-2BBE-48D7-9A05-F33D3A90A9FA@gmail.com> Date: Tue, 4 Jun 2019 17:34:07 +0300 To: pgsql-performance@postgresql.org X-Mailer: Apple Mail (2.3445.104.11) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_425C4E82-D30B-4885-B8C4-023982CC7F92 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii Hello everyone,=20 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 plan for 9.4 The plans are obfuscated , apologies for that but what stands out is the = following : Hash Left Join = (cost=3D200,673.150..203,301.940 rows=3D153,121 width=3D64) = (actual time=3D1,485.883..284,536.440 rows=3D467,123 loops=3D1) Hash Cond: (lima_sierra(six_lima_november2.victor_romeo, 1) =3D = foxtrot_hotel.victor_romeo) Join Filter: (whiskey_uniform1.six_sierra =3D = 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 --Apple-Mail=_425C4E82-D30B-4885-B8C4-023982CC7F92 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii 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
plan for 9.4

The plans are obfuscated , apologies for that but what = stands out is the following :


Hash Left Join (cost=3D200,673.150..203,301.940 rows=3D153,121 = width=3D64) (actual time=3D1,485.883..284,536.440 rows=3D467,123 = loops=3D1)
  • Hash Cond: = (lima_sierra(six_lima_november2.victor_romeo, 1) =3D = foxtrot_hotel.victor_romeo)
  • Join Filter: = (whiskey_uniform1.six_sierra =3D 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
= --Apple-Mail=_425C4E82-D30B-4885-B8C4-023982CC7F92--