Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ljzBX-00087k-Dq for pgsql-performance@arkaria.postgresql.org; Fri, 21 May 2021 07:03:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ljzBV-0005eN-3n for pgsql-performance@arkaria.postgresql.org; Fri, 21 May 2021 07:03:01 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ljzBU-0005eD-7G for pgsql-performance@lists.postgresql.org; Fri, 21 May 2021 07:03:00 +0000 Received: from sonic305-20.consmr.mail.ne1.yahoo.com ([66.163.185.146]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ljzBQ-0002HH-PE for pgsql-performance@postgresql.org; Fri, 21 May 2021 07:02:58 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1621580574; bh=wlqwwbVPeuwoSFKXAl9xwIofpHGz2XlAdJE9vATO1gg=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject:Reply-To; b=ZXGmfp8MCQ9xZr6kirXxqjb5VnijcytrTC6VM2avLavtlUe1Vc/w5f9BiaQLuOV6xtCCbMcEkB+VsnBS9YOScUTHDraDHH4MLEsITXPTFroz4oKI2BGCGDc8WNVe3oyUjfCLk1F/gYrCFpHq81dWPTSUZKDTnj0XEqQMCCzUuICsXmi9f7HHa9fOTGubMSsblsPubrknJJpPIZUjnDRYTMonp6qohYPXDVXwORVz1uGpqqyX82ln7FkqSp7+UjX6quzKEBPidzFNpWmjJusCNjO08Mkr7SXReqNWCEwjwHmNG9syfMA76lXgAXjhjs9nOOWmrpJOvsMtndlMpsJhdA== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1621580574; bh=2oBVfhKen0Ok5pVj7g2X1G+oGaI1xvIKEPLKMc3KwPN=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=D66W8myL4m5Wbtd+yboUZOORiXJvVBkePJDH09UdP/I2xsjq42M/DxJObgMHzBQOCr0Lp8KKdIhm90VLlBht9kNo2TR+DEP1hHw1/RlG2unj1HOjUBNAAG2Us5ruOw38BTjq+rCcnxpK/1+byo3JtYIRi4o/TNHjY20oQzU7Hu5+kd/zuP7uuy4R+poCXizO2B0rRhLhW9Y9bJA7U8ZDcoR2ej4BQyoq+dYTiTNAFkKJWe9ICasa1BisTB8axSJSZJZdjndP1T6QyXp1Zr7qLnkzP9THRuOuAngv3OGhU59g4suwHYfN9NHPDXZP1Wj5KFpw77nAaM9aMq/ZDqxzRw== X-YMail-OSG: ADgEjOMVM1kh.2Li2N7uIz0YSR67tRHJF5yRkFnwSQImmMbngwgJs0uQqiI63ol LOBztDHtK7RvoSReXo3BTpw09h.TN9zjszIHxDeS_7pR50h4BYj3BNqHTyOdJbLkU1eFE.._xlv6 V2FSNnUaZjzwXqaJIllM8BesDNKSFBtGLoc61_KEo4e5LKSXDXR4e54tGK1tl9JVq4DVD5bBGx3p WxZwBpFHSiL7xrcrZOchjhgFS.K2GKqQ_sOrqvm_FxAL8IJtwTMCl70HQijqXIi5_A_sw59UwbOB 6nA4WfOhhNnAIs_tYqmjgXD7zFdry5VZawzUytmXHl5_0.1t6QxZ9KQKKAqjZWgx2MtdcOgIm42b MotzELnFDOrTFGGZ9DhQ4j9VJb9sBQE4LQLf15iax7OdXcFFcLtX40ktnuXDVo6EFrKnMtBUHt73 qjv3f8Ithp8VsdbIf68WdRF4Im1XNZT29r8DlGmgY59BpJnzHjReWGAi8IlHeLfApYnrn6qnmSWd XAts.6jpedZiQhP4dO89R3sCIi9W849z3wu1pgXXCw2L1gfBgheveJvFtKE2YBazqBfvsT_H0zlG BrIYoRUOqmlSeV5TBR96dyAFtLnGfIEvP.jzQ4vPJabY1tzehwaT8FRZ857GhwCjNNyyzLmwlhwz AzVPsxa448zI.cvDsYa8mIdXWb4HyWWbv8JM6nglYMm3f9VCXGmqwoILzZYw03mRjKRzhILw77cB TW5wFGcmw3kKo6BLU4YS5uzVXOEVnaoQx4BON7GoQRM1McA57pf.wDg_xWzxUF0B8XQTT43YILNq 6X7EZtsmcUKAa8wPDJDGYFGpbdaQXCN_Yjmhan6yIjvypQOK5BwnI7Is..cA672PQhHPlRIcUfDm VEMTk1xWe_fRaKSQxLvbNSUDbk78kp0otjL7sFnwVSrGm12sKLRNZbP.77mdF2Gq2Muvygy2L_j9 deIVnYcFCDT4yFq7YgdmoVQSiEG3gRlS._jT7TNGB4.C1_mwK06uC6flHHQzsrqDn2oLWOSxWo0s ycXnOBsGK3k2F_qOTANFUXsW_iHsWkbBifJAdcvm63e_t09obpq1aDYsSpJzBVPmOG0HiZjGl9iq w3D3aSER0Cv42HS187YPIgfife5jaeSDMmPwWsU1CUZrIQMFiTmTGo7MTEhx0iY.plx8iXH0wAMN cXN_eCqlD2m1Szgs0TMGjcupX4YwmvCQnwfmn76dziPuhwHtFZhLScog.231WvsfMAPffCc_FV9f oINCJjk.vf7HaIdfNTzWAW1V3t9bPH0oR7zPbCcQyq_pk_P4674e_iaBqBfc7Anzmw0EBV1v3f81 g0rrLhru63s2Ydq1C9peLbOlotfQfIGzeb0mPcrIeZ21.E_b6MqWwcRpJ371syxqs83ytPP6nioB SWm9fNXnaZgHpzzBb8KuEdF_8Oegy5J8fxnCep3zIr1sQ0gQnIWUWzmBP.SU4cwWhyjyuquzFiqC 9Wm00KNQ8YgI.ztHkvCTyE4mp.sOiJUYeHtUcXygm9J4q_CF8Za_MXu8ixjjlwkzm3hv8WyIvYDW sfHNflTvpK_JnvggEVxvMbq4gkfSnCS9u2bVtAHhZeo_T.1p_7IV5K8KPNN8F39BI4l0S9rlbRYs OX_Rt8grOZz62AqMWJtmxDXMMwuJWogZFdz1hQaowuVhpWmlE7O53lwtWZX5FvO6mg3q67WyT9LU B783sP6iVcGUOF_7WTGoaJ8uu7nIhlfbwP14W.ZamWItEMbZWJJIt7mPHJpdwwUbtdZA5rdrmuDG bv3fEV8M437Lt21Jop.1xi8AwZ9BHniL5O6gqDFdaLcYdbxZxzY_hR0HtNF6xDBJXAaHr0a65yKx hDfs3HLPyKwt7.tUbz8TFDeVUt9.f5DRfyqHwX82tt7.HfZKY8Yh0EzY5E3dUp6Ruo4FRpYMblDE O9MOfgb1S7vwjMY_IP5KKEGIgj1kTgjVkPZ.PE13dCyyWIJGJ0T1IwKhKjeV7DYR41wUMl6GAWbX ZZ39Fn9GWCske1jrX.qk2eBhXbTwzoSO23AFD2oN7fGXkjyQV04ftZKMflOpNQbk88eOBNSyrEJf K3wHi6B6x0Ge6MOF7rEsCainf4iHkzgjz7eU4BEXT0mbPZdmGOB6kiW4hCZpXeriOSSUa77zJuBG CfmoavYfrFZJnGmVyaLo52I3zmubg2uoE_o0_uaFqnzn3uMPa2hID_i.Q64QtwDQdmOoyYJyXZem sFGQ7QNaVFBd.EpYVdco7gQ7pvHAuRSonFNQgwlmKSPQcpO4Y0cMQvrm6trQI60VRNlZBvBq3Gwz QAuU7SREhColr1ioySyjNj18j8NUr08KSxfFLo1usaScObQlGFGWLS9N_jwBTPEfJbnZaGSayRa6 JlqudxWYofkkJxbBNTIwi2nA2cF75U66YFK44pid.gpjhSBATPPN86_jL9aHojOaov_toB_aAdvZ 2sgjBE.f2XIS0Ep8El.m4jue.0.gym9L_sqpWR7B6iLvIISTFvYIiQ43wk55NXnt_iXSVDkn6SJL .YX5xucrDr6IdkkO_vPdt50z_h40JUq8omMEj7KDVDz88ybFThg6TsIRpczI6O5C7jf4Dnw-- X-Sonic-MF: Received: from sonic.gate.mail.ne1.yahoo.com by sonic305.consmr.mail.ne1.yahoo.com with HTTP; Fri, 21 May 2021 07:02:54 +0000 Date: Fri, 21 May 2021 07:02:51 +0000 (UTC) From: Nagaraj Raj To: Justin Pryzby Cc: David Rowley , Pgsql-performance Message-ID: <81358482.168153.1621580571840@mail.yahoo.com> In-Reply-To: <20210521023836.GH3676@telsasoft.com> References: <87840670.92362.1621557131080.ref@mail.yahoo.com> <87840670.92362.1621557131080@mail.yahoo.com> <1347719485.144821.1621564574978@mail.yahoo.com> <20210521023836.GH3676@telsasoft.com> Subject: Re: Partition with check constraint with "like" MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_168152_201205428.1621580571839" X-Mailer: WebService/1.1.18368 YMailNorrin Content-Length: 8524 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_168152_201205428.1621580571839 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable So what about 'Z' or 'z' and 9? I created the partitions=C2=A0tables=C2=A0FROM (A) to (B) ;FROM (B) to (C) = ; .. FROM (Y) to (Z) ; then what would be the range of ZFROM (Z) to (?) ; =C2=A0same way for 9=C2=A0 On Thursday, May 20, 2021, 07:38:50 PM PDT, J= ustin Pryzby wrote: =20 =20 On Fri, May 21, 2021 at 02:36:14AM +0000, Nagaraj Raj wrote: >=C2=A0 Thank you. This is a great help.=C2=A0 > But "a" have some records with alpha and numeric.=C2=A0 So then you should make one or more partitions FROM ('1')TO('9'). > example : > insert into mytable values('alpha'),('bravo'); > insert into mytable values('1lpha'),('2ravo'); >=20 >=20 >=C2=A0 =C2=A0 On Thursday, May 20, 2021, 06:23:14 PM PDT, David Rowley wrote:=C2=A0=20 >=C2=A0=20 >=C2=A0 On Fri, 21 May 2021 at 12:32, Nagaraj Raj wr= ote: > > I am trying to create partitions on the table based on first letter of = the column record=C2=A0 value using inherit relation & check constraint. >=20 > You'll get much better performance out of native partitioning than you > will with the old inheritance method of doing it. >=20 > >=C2=A0 EXECUTE 'CREATE TABLE partition_tab.' || c_table || '(check ( nam= e like '''|| chk_cond||''')) INHERITS (' ||TG_TABLE_NAME|| ');'; >=20 > This is a bad idea. There's a lock upgrade hazard here that could end > up causing deadlocks on INSERT.=C2=A0 You should just create all the tabl= es > you need beforehand. >=20 > I'd recommend you do this using RANGE partitioning. For example: >=20 > create table mytable (a text not null) partition by range (a); > create table mytable_a partition of mytable for values from ('a') to > ('b'); -- note the upper bound of the range is non-inclusive. > create table mytable_b partition of mytable for values from ('b') to ('c'= ); > insert into mytable values('alpha'),('bravo'); >=20 > explain select * from mytable where a =3D 'alpha'; > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 QUERY PLAN > ------------------------------------------------------------------- >=C2=A0 Seq Scan on mytable_a mytable=C2=A0 (cost=3D0.00..27.00 rows=3D7 wi= dth=3D32) > =C2=A0 Filter: (a =3D 'alpha'::text) > (2 rows) >=20 > The mytable_b is not scanned. =20 ------=_Part_168152_201205428.1621580571839 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
So what about 'Z' or 'z' and= 9?

I created the partitions tab= les 
FROM (A) to (B) ;
FROM (B) to (C) ;
.<= /div>
.
<= span>FROM (Y) to (Z) ;<= br>

then what would be the ran= ge of Z
FROM (Z) to (?) ;
 
s= ame way for 9 
=20
=20
On Thursday, May 20, 2021, 07:38:50 PM PDT, Justin Pryz= by <pryzby@telsasoft.com> wrote:


On Fri, May 21, 2021 at 02:36:14AM +0000, Nagaraj Raj = wrote:
>  Thank you. This is a great help. <= br clear=3D"none">> But "a" have some records with alpha and numeric.&nb= sp;

So then you should make one or mor= e partitions FROM ('1')TO('9').


> example :
&= gt; insert into mytable values('alpha'),('bravo');
> i= nsert into mytable values('1lpha'),('2ravo');
>
>
>    On Thursday, May 20= , 2021, 06:23:14 PM PDT, David Rowley <dgrowleyml@gm= ail.com> wrote: 
> 
>  On Fri, 21 May 2021 at 12:32, Nagaraj Raj <nagaraj.sf@yahoo.com> wrote:
> > I am t= rying to create partitions on the table based on first letter of the column= record  value using inherit relation & check constraint.
>
> You'll get much better performance o= ut of native partitioning than you
> will with the old= inheritance method of doing it.
>
= > >  EXECUTE 'CREATE TABLE partition_tab.' || c_table || '(check= ( name like '''|| chk_cond||''')) INHERITS (' ||TG_TABLE_NAME|| ');';
>
> This is a bad idea. There's a lo= ck upgrade hazard here that could end
> up causing dea= dlocks on INSERT.  You should just create all the tables
> you need beforehand.
>
&g= t; I'd recommend you do this using RANGE partitioning. For example:
>
> create table mytable (a text not nu= ll) partition by range (a);
> create table mytable_a p= artition of mytable for values from ('a') to
> ('b'); = -- note the upper bound of the range is non-inclusive.
&g= t; create table mytable_b partition of mytable for values from ('b') to ('c= ');
> insert into mytable values('alpha'),('bravo');>
> explain select * from mytable= where a =3D 'alpha';
>         &n= bsp;                   QUERY P= LAN
> ------------------------------------------------= -------------------
>  Seq Scan on mytable_a myta= ble  (cost=3D0.00..27.00 rows=3D7 width=3D32)
> &= nbsp; Filter: (a =3D 'alpha'::text)
> (2 rows)
>
> The mytable_b is not scanned.


------=_Part_168152_201205428.1621580571839--