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 1igpSv-0003By-Nl for pgsql-performance@arkaria.postgresql.org; Mon, 16 Dec 2019 12:27:10 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1igpSu-00070S-HC for pgsql-performance@arkaria.postgresql.org; Mon, 16 Dec 2019 12:27:08 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1igpSu-00070K-0B for pgsql-performance@lists.postgresql.org; Mon, 16 Dec 2019 12:27:08 +0000 Received: from smtp54.i.mail.ru ([217.69.128.34]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1igpSq-0001Je-LF for pgsql-performance@lists.postgresql.org; Mon, 16 Dec 2019 12:27:06 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=mail.ru; s=mail2; h=Content-Type:MIME-Version:Message-ID:Date:Subject:In-Reply-To:References:Cc:To:From; bh=OlsGTY+PedVreMbqKZ68qR6308+7ucXuHNhbxv8EPEg=; b=rFOvvKj3N6fbau7XaXDWI/+hUXPzw5q5ZIyrJmuH9Y+027/g9VvU++NYnYH64MMfNuaIBRy/tKSmRFzLHyWcewoHc2xhL/B8Wo2dYR+JkG67cQIqS/k0LNGYptDiNjiU2BaRPlYfxxM7AC6cRAWozOlN2wF4zGPe4+FXStTjMqw=; Received: by smtp54.i.mail.ru with esmtpa (envelope-from ) id 1igpSn-0004MH-Cy; Mon, 16 Dec 2019 15:27:01 +0300 From: "Andrew Zakharov" To: "'Mariel Cherkassky'" , "'Jeff Janes'" Cc: References: In-Reply-To: Subject: RE: performance degredation after upgrade from 9.6 to 12 Date: Mon, 16 Dec 2019 15:27:01 +0300 Message-ID: <017001d5b40c$1e083800$5a18a800$@mail.ru> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0171_01D5B425.4355BE20" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQHgqkmAj5PzhY2bKDBVRsDoC4IlQAC9tboBAqCuX7oCrD2++QF5Jkkwp2qfZ9A= Content-Language: ru Authentication-Results: smtp54.i.mail.ru; auth=pass smtp.auth=andrew898@mail.ru smtp.mailfrom=andrew898@mail.ru X-7564579A: 646B95376F6C166E X-77F55803: 0A44E481635329DB0E1AA8A03B392317D32E5E488652173698210C0F58CEE5366E75DFCD895BCC45F688BCB05C26794D735BAB88EB1AB2554218143E5BE09C51FD5EB0547051DAA8630B8CC69C1CFCE0090598096E5E7227 X-7FA49CB5: 0D63561A33F958A578772BC4C2E0E3EDFF0A7FB2EA334886B9BCC01CE9CFFCEA8941B15DA834481FA18204E546F3947C6089696B24BB1D19F6B57BC7E64490618DEB871D839B7333395957E7521B51C2545D4CF71C94A83E9FA2833FD35BB23D27C277FBC8AE2E8B8883BAB8B32E402CA471835C12D1D977C4224003CC8364767815B9869FA544D8D32BA5DBAC0009BE9E8FC8737B5C22492AA437DA6D0FD5573AA81AA40904B5D9CF19DD082D7633A063753DB72453AD9A3AA81AA40904B5D98AA50765F7900637D2C6F5AE34F7A4E5EC76A7562686271ED187B4DA314F1B5535872C767BF85DA227C277FBC8AE2E8B8EE66389869CF301A0CA5B58A249987535872C767BF85DA2F004C906525384306FED454B719173D6462275124DF8B9C9DE2850DD75B2526BE5BFE6E7EFDEDCD789D4C264860C145E X-Mailru-Sender: C0867E0E6D7FB6C0AC1DF044B4F6B17065F189F01DCB9D0A1B30181EC02EFBCCCF113DD4F50517E876CADC040CC768ECC77752E0C033A69E4431F22F943D8E2367DBD6A6546105283453F38A29522196 X-Mras: OK List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_0171_01D5B425.4355BE20 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi there =E2=80=93 I have no idea why this happening. But I suspect the parallel requires = more internal machine resources like CPU etc because you can faster = retrieve the disk data from the one hand but you ought to spend more = resources for maintaining several threads and theirs coordination (px = coordinator process in Oracle terms) from another one. Thus there could = be more serious hardware requirements even just to keep performance the = same. I believe that the real benefit of the parallel will be shown when = you have pair of large and wide tables (30M or more each) with hash join = (typical task for mart construction) but such class of databases is = supposed to be big and required enough resources initially. =20 =20 From: Mariel Cherkassky =20 Sent: Monday, December 16, 2019 2:48 PM To: Jeff Janes Cc: Andrew Zakharov ; = pgsql-performance@lists.postgresql.org Subject: Re: performance degredation after upgrade from 9.6 to 12 =20 Hey Jeff,Andrew, I continued testing the 12version vs the 96 version and it seems that = there is almost non diff and in some cases pg96 is faster than 12. I = compared the content of pg_stat_statements after each test that I have = done and it seems that the db time is almost the same and sometimes 96 = is faster by 5%. =20 Any idea why there isnt any improvement even when I enabled the parallel = params in 12 ? I can add a few examples if needed.. ------=_NextPart_000_0171_01D5B425.4355BE20 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Hi there =E2=80=93

I have no idea why this happening. But I = suspect the parallel requires more internal machine resources like CPU = etc because you can faster retrieve the disk data from the one hand but = you ought to spend more resources for maintaining several threads and = theirs coordination (px coordinator process in Oracle terms) from = another one. Thus there could be more serious hardware requirements even = just to keep performance the same. I believe that the real benefit of = the parallel will be shown when you have pair of large and wide tables = (30M or more each) with hash join (typical task for mart construction) = but such class of databases is supposed to be big and required enough = resources initially.

 

 

From:<= /b> = Mariel Cherkassky <mariel.cherkassky@gmail.com>
Sent: = Monday, December 16, 2019 2:48 PM
To: Jeff Janes = <jeff.janes@gmail.com>
Cc: Andrew Zakharov = <Andrew898@mail.ru>; = pgsql-performance@lists.postgresql.org
Subject: Re: = performance degredation after upgrade from 9.6 to = 12

 

Hey Jeff,Andrew,

I continued testing the 12version vs the 96 version = and it seems that there is almost non diff and in some cases pg96 is = faster than 12. I compared the content of pg_stat_statements after each = test that I have done and it seems that the db time is almost the same = and sometimes 96 is faster by 5%.

 

Any idea why there isnt any improvement even when I = enabled the parallel params in 12 ?

I can add a few examples if = needed..

------=_NextPart_000_0171_01D5B425.4355BE20--