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.92) (envelope-from ) id 1j6Jep-0002Uc-C1 for pgsql-performance@arkaria.postgresql.org; Mon, 24 Feb 2020 19:44:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1j6Jeo-00017j-4i for pgsql-performance@arkaria.postgresql.org; Mon, 24 Feb 2020 19:44:46 +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 1j6Jen-00017c-QG for pgsql-performance@lists.postgresql.org; Mon, 24 Feb 2020 19:44:45 +0000 Received: from sonic311-14.consmr.mail.bf2.yahoo.com ([74.6.131.124]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1j6Jek-0004kh-4x for pgsql-performance@lists.postgresql.org; Mon, 24 Feb 2020 19:44:45 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1582573479; bh=YsaSoaKPc/GWpCyXv4+2QnZEs2CeMOp5YfJrvDsP0iQ=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject; b=kHYfFd0CMOxZsnayGtILnxmKAEu/sdYsAlmjiCbDJW2v9waCEghSN97u4QzxquPWvgDb06ZbidU4/ktEyrOqqP5V+4KF3MePiNbzlylCKRpX+lumSD6nER1aIctHmCt2E+It6+1lZ1gT2Hn7AoUAPOpDx5HkxuM6jlwBMFJhaMQ7C0Sq74d5+Qw4r533ig3SMPLQip19MdA5zIh2O54LJ4wKc52Y+C6GOSHXnHkXw49drDs1UgzxfJB/7wseaFn4u9xab6SVb2URRF0WJftyOyq5jWFvTfhKemN8F+jXaAtjyx4kcAFjivg10Azxb+HsCHAECv+kmO67EbOo3/dIew== X-YMail-OSG: 2bj5uOgVM1lQ_3O7jfm5Q0vQpBmHuNw7bTHRLD8R3v.UwQD1i98IeyGuEI.NtTm 65e3dta9dgld9rjmNuh_NZnjv0v0RQ6Ju83TP.Vhk_TaDWiTISEFOyZcDXOS_z8s5MGniGKgAFgb TY3IPkZzkckCSxazdTmiBj6dSORqYm.nuG0P_p9C4A3VDNydOs3obtY96oR5063G5iek1yK7lY94 FQZbcOhD.Mmoeswd2WVq8fx7wXHGEetvEeqmjxwz9Acs2gVq6s3cRLZ6sJgSFbslh3Hrym4ZRB.h eASg8kRw715op5TMo1KLkf.D339uRNgqKuNT6haQLaEqNdiu_w6DkwESVLzDfgQg3KeNy05bM7fC 4gw0srUYDVER28O4lpiUm9FZsBdYZaErzkRZzfZwsiW7e7LA8GHzfi6I79CSVnPNfttPTFHLIuUI G_gUfHr6ezsBUIsptWwcL5QIhR.5OpqlG3bm9ZADUVdjlLOxcTJeoD0GdWkmSo9ErBbZWrSpoMrC NsNCZDg0nHpC4w286iRloSL16Xi6SN.T8bgJkGuoiZTMQBnZ5Smg90.0VODjEHUZWOFk1gri_aB0 df5Yf0qJCcGi0kEOJ67qgmYZBbZ0Cko5iP7h7F2Ng8icsc7PVjbQcSABsmp7pQpgYm2fUveQpUHt NuxtVfmBO3xa3bah9TAWodV1o2JFh0hMthaosr8DBggeeDKAdWORAZ2IGiu._tEzi9BjDSjSJAQ5 nU.TZOnrYiQG8kOcoGsULc.7aKJ4qmrWI5Nlfr1CegQGNAn2Hom3rsQzQN9ikR3DHlouXpKxfzvC dzYNn9q9kxTi43UhrwFmyORrefRATncjK4S0kGV9PQxP7s7NugxowRssiAJjQPxjSQk8.FnWHCsG rUOfgIeOYhsiscynUSixQaoK9pVKCDZuxOCYdGYwCADtqnsg24k7AUlYGOQTmjpd3LZiuPSxoud. Bck4EKEDJchK56XyD6ccOHX2AOIpH25hsJ8uMetQ_A1T8cITHnq6N2f91ocjiTWYZFu.5AodJ9f_ h88SICsROmsOodLhyRNLmWAnr8TTgtJ3Yuba873rzi5vCXQpQJerSVnwmK1QXRHaNKdlX9POWq7l Fau1nDXKdewZFWtPdr1tZmRzQ2tnTg3OxHR7kkWIrnEDFi.Zfj9jeUMin7PwnRkUbzZcDu.kxFEz 0V55eL0Kh0OYVyuGjTC.3AGh.mT2hizGI3wWNVC3z10c3lH0i11iPZ6FboY2gtm6Ehri4WHmL5VG Uk.AFTjXCMLVUI9nADqXtwBzwirpNSSQltM1XyWvuuzCdZVk_BUyXcAaQmh55Z0nG7xzOnonBFVN JINJ3Q7L7Y1ewJmxIgV3wNxstfsRlZueVsXvYZQlp_q3FpUD815k- Received: from sonic.gate.mail.ne1.yahoo.com by sonic311.consmr.mail.bf2.yahoo.com with HTTP; Mon, 24 Feb 2020 19:44:39 +0000 Date: Mon, 24 Feb 2020 19:44:34 +0000 (UTC) From: Ravi Garg Reply-To: Ravi Garg To: Imre Samu Cc: pgsql-performance@lists.postgresql.org Message-ID: <1733010.5446408.1582573474502@mail.yahoo.com> In-Reply-To: References: <1789489940.5008704.1582451790367.ref@mail.yahoo.com> <1789489940.5008704.1582451790367@mail.yahoo.com> <20200223101209.GU31889@telsasoft.com> <1169437250.5002073.1582455449880@mail.yahoo.com> Subject: Re: PostgreSQL 11 higher Planning time on Partitioned table MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_5446407_1216826382.1582573474499" X-Mailer: WebService/1.1.15199 YMailNorrin Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.97 Safari/537.36 Content-Length: 12490 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_5446407_1216826382.1582573474499 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable > IF txnid is real UUID , then you can test the https://www.postgresql.org/= docs/11/datatype-uuid.html performance> see https://stackoverflow.com/quest= ions/29880083/postgresql-uuid-type-performance> imho: it should be better. Sure, thanks=C2=A0Imre Thanks and Regards, Ravi Garg On Sunday, 23 February, 2020, 09:49:00 pm IST, Imre Samu wrote: =20 =20 > ...=C2=A0 txid character varying(36) NOT NULL, > ...=C2=A0WHERE txnid =3D 'febd139d-1b7f-4564-a004-1b3474e51756'>=C2=A0The= re is only one index (unique index btree) on 'txnID' (i.e. transaction ID)= =C2=A0character varying(36). Which we are creating on each partition. IF txnid is real UUID , then you can test the=C2=A0https://www.postgresql.o= rg/docs/11/datatype-uuid.html=C2=A0performancesee=C2=A0https://stackoverflo= w.com/questions/29880083/postgresql-uuid-type-performanceimho: it should be= better. best,=C2=A0Imre Ravi Garg ezt =C3=ADrta (id=C5=91pont: 2020. febr. 23= ., V, 11:57): Hi Justin, Thanks for response. Unfortunately we will not be able to migrate to PG12 any time soon. =20 - There is only one index (unique index btree) on 'txnID' (i.e. transact= ion ID) character varying(36). Which we are creating on each partition. - Our use case is limited to simple selects (we don't join with the othe= r tables) however,=C2=A0we are expecting ~70 million records inserted per d= ay and=C2=A0there would be couple of updates on each records where average = record size would be ~ 1.5 KB.=C2=A0 - Currently we are thinking to have Daily partitions and as we need to k= eep 6 months of data thus 180 Partitions.However we have liberty to reduce = the number of partitions to weekly/fortnightly/monthly, If we get comparabl= e performance. =20 - We need to look current partition and previous partition for all of ou= r use-cases/queries. Can you please suggest what sort of combinations/partition strategy we can = test considering data-volume/vacuum etc. Also let me know if some of the pg= _settings can help us tuning this (I have attached my pg_settings). Thanks and Regards, Ravi Garg, Mob : +91-98930-66610=20 On Sunday, 23 February, 2020, 03:42:13 pm IST, Justin Pryzby wrote: =20 =20 On Sun, Feb 23, 2020 at 09:56:30AM +0000, Ravi Garg wrote: > Hi, > I am looking to Range Partition one of my table (i.e. TransactionLog) in = PostgreSQL 11.While evaluating query performance difference between the un-= partitioned and partitioned table I am getting huge difference in planning = time. Planning time is very high on partitioned table.Similarly when I quer= y by specifying partition name directly in query the planning time is much = less **0.081 ms** as compared to when I query based on partition table (par= ent table) name in query, where planning time **6.231 ms** (Samples below).=
That's probably to be expected under pg11: https://www.postgresql.org/docs/11/ddl-partitioning.html |Too many partitions can mean longer query planning times... |It is also important to consider the overhead of partitioning during query= planning and execution. The query planner is generally able to handle part= ition hierarchies with up to a few hundred partitions fairly well, provided= that typical queries allow the query planner to prune all but a small numb= er of partitions. Planning times become longer and memory consumption becom= es higher as more partitions are added > There are around ~200 child partitions. Partition pruning enabled.Postgre= SQL Version: PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) = 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit How large are the partitions and how many indexes each, and how large are t= hey? Each partition will be stat()ed and each index will be open()ed and read() = for every query.=C2=A0 This was resolved in pg12: https://commitfest.postgresql.org/21/1778/ --=20 Justin =20 =20 ------=_Part_5446407_1216826382.1582573474499 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
> IF txnid is r= eal UUID , then you can test the https://www.postgresql.org/docs/11/datatyp= e-uuid.html performance
> see https://stackoverflow.com/questi= ons/29880083/postgresql-uuid-type-performance
> imho: it shoul= d be better.

Su= re, thanks Imre
=

Thanks and Regards,
Ra= vi Garg

=20
=20
On Sunday, 23 February, 2020, 09:49:00 pm IST, Imre Sam= u <pella.samu@gmail.com> wrote:


> ...  txid character varying(36) NOT NULL,
> ... WHERE txnid =3D 'febd139d-1b7f-4564-a004-1b3474e51756'
&g= t; There is only one index (unique i= ndex btree) on 'txnID' (i.e. transaction ID) character varying(36). Which we are creating on each partition.
<= span style=3D"color:rgb(0,0,0);">
IF txnid is real UUID , then you can test the&= nbsp;https://www.postgresql= .org/docs/11/datatype-uuid.html performance
<= div>imho: it should be better.

best,
 Imre


Ravi Garg = <ravi.garg@yahoo.com> ezt =C3=ADrta (id=C5=91pont: = 2020. febr. 23., V, 11:57):
=
Hi Justin,

Thanks for response.

Unfortunately we= will not be able to migrate to PG12 any time soon.
  • There is o= nly one index (unique index btree) on 'txnID' (i.e. transaction ID) c= haracter varying(36). Which we are creating on each partition.
  • Our use case is li= mited to simple selects (we don't join with the other tables) however, = ;we are expecting ~70 million records inserted per day and&nb= sp;there would be couple of updates on each records where average record si= ze would be ~ 1.5 KB. 
  • Currently we are thinking to have Daily= partitions and as we need to keep 6 months of data thus 180 Partitions.How= ever we have liberty to reduce the number of partitions to weekly/fortnight= ly/monthly, If we get comparable performance.
  • We= need to look current partition and previous partition for all of our use-c= ases/queries.
Can you please suggest = what sort of combinations/partition strategy we can test considering data-v= olume/vacuum etc. Also let me know if some of the pg_settings can help us t= uning this (I have attached my pg_settings).


Thanks and Regards,
Ravi Garg,
Mob : +91-98930-66610


=20
=20
On Sunday, 23 February, 2020, 03:42:13 pm IST, Justin P= ryzby <pryzby@telsasoft.com> wrote:


On Sun, Feb 23, 2020 at 09:56:30AM +0000, Ravi Garg wr= ote:
> Hi,
> I am looking to Rang= e Partition one of my table (i.e. TransactionLog) in PostgreSQL 11.While ev= aluating query performance difference between the un-partitioned and partit= ioned table I am getting huge difference in planning time. Planning time is= very high on partitioned table.Similarly when I query by specifying partit= ion name directly in query the planning time is much less **0.081 ms** as c= ompared to when I query based on partition table (parent table) name in que= ry, where planning time **6.231 ms** (Samples below).<br>

That's probably to be expected under pg11:

= https://www.postgresql.org/docs/11/ddl-partitioning.html
|Too many partitions can mean longer query planning times...
|It is also important to consider the overhead of partitioning du= ring query planning and execution. The query planner is generally able to h= andle partition hierarchies with up to a few hundred partitions fairly well= , provided that typical queries allow the query planner to prune all but a = small number of partitions. Planning times become longer and memory consump= tion becomes higher as more partitions are added


> There are around ~200 child partitions. Partition= pruning enabled.PostgreSQL Version: PostgreSQL 11.7 on x86_64-pc-linux-gnu= , compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit


How large are the partitions and how many= indexes each, and how large are they?
Each partition wil= l be stat()ed and each index will be open()ed and read() for
every query.  This was resolved in pg12:
https://commitfest.postgresql.org/21/1778/

--
Justin

------=_Part_5446407_1216826382.1582573474499--