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 1ljv1Z-0000El-7M for pgsql-performance@arkaria.postgresql.org; Fri, 21 May 2021 02:36:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ljv1W-0000fG-CT for pgsql-performance@arkaria.postgresql.org; Fri, 21 May 2021 02:36:26 +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 1ljv1V-0000f8-VZ for pgsql-performance@lists.postgresql.org; Fri, 21 May 2021 02:36:26 +0000 Received: from sonic308-10.consmr.mail.ne1.yahoo.com ([66.163.187.33]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ljv1P-0000BU-7k for pgsql-performance@postgresql.org; Fri, 21 May 2021 02:36:24 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1621564577; bh=PDvslef7+9/DrbrHnzERtpoCOINQ2VU03w8GujPYHOc=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject:Reply-To; b=FM4+cpLE8u9p1Dnk+CiRzRihSTCNlx1yE5li+Ooc5Yh+xVSmSdijoXs+KUPjV3VHpFNXTKpzVrSfs+IPvAvn6kkuRiOAlKKbL8llMcaq4z6RA3LoSsUc7hmburQeLX5tcAFj3feCtQUh95hoarOmj4Moeqgn1xTBjOG+D5F/7S6JLmm4P3YmKx/LjfqzybzXGaIs/z6LrgMuQfEXISgN8MDFCntrMddiN6cZ2bgEqO0oGhgHVOXlnp9UbsgNVjWt1piJP35gWtMVPlciSNyvfEl1gbHfVqx08+dJ8Wo6+/8AB6pt4GFBc0LtD3bgJ+TRrwshjdKyr9GCa67OyXuF2g== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1621564577; bh=8ofkH67qqDAR2GT/3YHfuML5eo/pUV9cUKpUbiKo4PD=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=PsZdwkQSq5ggU3YrkAu26QtD32Vrxb24yK1ycQjBxObq04ao6ch2uXLBN8N7VxiAR02HDLc33LeJFxv+k4A0XXndbL2M6mYT/OFOrfPDIFgQkZvgJqvQz22Xdk+cCFkIZa+WW2GzRzGARtRZxPtraE2yZs51TggRNb5W9cB2McoWSt5l5drOhCLTvdpHnzypEgAjdXSLQCoq8m9cnDo3ulQA+YQlG8QUQRxg+OqLwypd0AYiVfWY+BLAw4oJyAKWEFUSxZjJ7oWKyJtW8/SXu81FD8QP78s5f+J88Lj5bo2tdonRd6zU2aOxhGTp+H2IOg+9bgmhzow97IkkR86ABQ== X-YMail-OSG: nDffRzUVM1lAn16wWOL_5r.G3mHArPRem0O4BC5AMRUXNEcLxNbQMMsBBR9PfUF NxtY4LeWSZ5jYFSa5iXmMlqaepfAhDe.GJulqcAv8MZhmxIZ8_KEZoHEFkMSu7S9qYDKlQa8GrIT RI7J2SAvDOxfR8XonDEbzLexXitw5zj48NoerRYo20_e8Kts8VPCZJYGmrlJUQymvoJ3DxJcu4fu 45dP5Uem3nLPUmBbtaGoVFt1DJqPp.TIfNoPW0AhCJdsKEnvnanQn5uxs2.FNzsoTZBqPhQePgDz FbimyDgHGHm0RPT.040MpiV..NEfWDmxOAqEYOn0LuzHE3_GaBolQdlgmHFXHsnnr.YzDh2eNBnS MviV1QguksA9Z5khuxbPW9dlI9OJLk3dOJ.XXjnl0HU2wmDT31icai4nDuWO1ChTgng8XGITyMQs 0pNNkXsUUTMTLUfnRoqQRyBxoCfzHXsKHEOixZOfXiHXwi.ZJYuPo92jsKVLHg2XCyJR.qlxlYzl bSK03nFU5nF73mliw1Xa5x__n4KtiupZJ.4wmxmCQ4c_RHjBMYfJz1SU_GaDfCdr2Qp8vAP2gL8u qwPNX8XVm1aoxlSMbNIsZeWMBNdeVZOlvdn57QUaw0_WGsf8KEQOlUMnJ91I6xINd9_ki94yNQ3J k5C4GMPLiuy7TnCRKEyCkZIkFZjD4LAcPRZd9VBYNgkAi7sAbTSKLLahq443A8wbbtfjRbyhA_vn 5PHIgj3Uo7V60.HUuCGjdOWAeKQ31jmBu.EAt9gKPkXdnnAYC314kfTqiCOwJ2cFabZmhKjzs0cX uGyumwAaGHUskXgO6iqQ09PonNYIL2NCn_qFdaqugsFXMLROnHxyKC6ZEUf.72KQT9vzxNRw9gKO VAjvGBgW_VJRf1ayXyFgLrRsc84Nv9bjsOfgWQ1kQfuCJctAudUODUEmcNobR5VO6ZlpejWVTYXT HK_gAioEBm6pzJzAjV.YwZqKBuxLWjdA9_VK7moGvHGnxlFa9afDZDGLaYkaX2CT0KlJQdKbxL_7 sPmkbBAvI4zGGWsK3b_unYYIKkutVeWXGAOnTCzVsfi5a3KpzXbGJGktFmd2xjWUGzRQ6hIw8cby YdvAtt89h3ErWyRuy7FzV3SwWJ44ZNmwWJez8MX52.b6ja42dDkGmvX4lIclqEtjGuEBxJS7RMBF oHBq7yjm5NVLt5kPlTzKk8WjbwFn1.O3qoH0fLHb4Feq2o98sEkEM6nfarDETeiIpTgY_pfMKWdd _uuHO73II1gfLjfGKT7gHnQcJRw_f1K_GV0huzrCCiEkkqnYZ2duPCJ0v707JOXL63I7bCsbO.fe pvjRz2MIqXVomggJHHmFOlyz3jsdpZIGw2k41etWtgaQHnkIi74vXZjNJ4U4bGQzNaLlOo3t9u5C XKr0vgK4BMIbUU3gBa1wlgUOl.5ER38LLkcZEf7KEq6BE74mV.0JNdhEJPuiwZmMNZ5XcqCyAj07 i2UWwG2H1isDZrPEeBcNIFqhRWEuznxiisiRMdT1LUUlMkh3H7YjUgBOin7O30a3CP58b.KoRCA0 OQdkD__ixikLDlIUeW1btDc2JabWXVPmCESnJtQMx_BnFRcK.IkVLXAaYVarI5SuM2e6FBu3vqRb KzZfdOq0_Kjpgm6kc28bKWZZKrQOdUNEL_zWlj5MKjRocKspHElzZPV_ibvHfVh.wJUqGxpPVGU_ RjcpnSZ7W_ICTwtYhN9PYiyGftm6chezX2E.lsXjDoNiatHR7PF5mMWqRhALYz5u7bUVb3aOuxfH EBDR.GCq5z.DzmhjzcdcNt6i.ZXLAOrTa8uteQQz7uGP_yl.6RiifZMNQMgT_zygCd.ZJ8HBSQ90 lP4DhMo06CdEK33fK65i9CEPsi0udTy14hg318Thb_copjFreHc90kdkpW.wS0A_9Vc8gigk__nd 758DWsUC.6w2g4gvkbLMKutNexbvW_bCQ1iOVEkrojwMcBzNq186kqxwctUTJJlB58tkm2r2XuVh XsBRViE0NChNhe3tX1_b_Qk1c3p7QXepk67y9J48ptJrc7PSRr_d606ylAIReMycqyPOrUky9Jq1 pBQOOEUKomlf3v0QcqfrSFp8I_78EASsXxbZwPmZPK.n_SDbUFQlyx2MEHYuFVdNq73xjOmV646i hbwzTRi5R2z6mRWEYSlRYKPK8L9W25JYHNjyyD9nepF_VRBJHcFLr6Q10su68ZAq32OXl3Py0y.C Nljvt2LVd_ogzqjvOCJwegZZCu9dB6gjM7Rz_ljjRj3kAKXhCMa.Pf5TwFWOX_a8U7n_uYN.TRmT u2dWRiB3JMUiKg3G43TTUN8Y0nGBKtf9m30Z1NAgB8ITzom6rYvbrFU6snHtkveM_K00llRL0iZM AF7Fd0nNob7X0yLF1eEUgSPMg9GxNuwmHocRIQtPST4yUx7dNfrvvOjxtgyZXxhWjZ5PaSwzpHQ_ O0GcAxX9xhhpxDd1FMoKjVoIWqDZldYMi4zBYCrKa5eygdroOR0RrrJbkBR38cztTsBr8FIgUSuQ gMgmicnmQRVmLFb2bwZAfYKkFQouw2.m7QGRwnPYJG65aaSEKvSNycPhKD4MF_9FjpWRLruH5 X-Sonic-MF: Received: from sonic.gate.mail.ne1.yahoo.com by sonic308.consmr.mail.ne1.yahoo.com with HTTP; Fri, 21 May 2021 02:36:17 +0000 Date: Fri, 21 May 2021 02:36:14 +0000 (UTC) From: Nagaraj Raj To: David Rowley Cc: Pgsql-performance Message-ID: <1347719485.144821.1621564574978@mail.yahoo.com> In-Reply-To: References: <87840670.92362.1621557131080.ref@mail.yahoo.com> <87840670.92362.1621557131080@mail.yahoo.com> Subject: Re: Partition with check constraint with "like" MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_144820_1336019550.1621564574976" X-Mailer: WebService/1.1.18368 YMailNorrin Content-Length: 5901 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_144820_1336019550.1621564574976 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Thank you. This is a great help.=C2=A0 But "a" have some records with alpha and numeric.=C2=A0 example : insert into mytable values('alpha'),('bravo'); insert into mytable values('1lpha'),('2ravo'); On Thursday, May 20, 2021, 06:23:14 PM PDT, David Rowley wrote: =20 =20 On Fri, 21 May 2021 at 12:32, Nagaraj Raj wrote: > I am trying to create partitions on the table based on first letter of th= e column record=C2=A0 value using inherit relation & check constraint. You'll get much better performance out of native partitioning than you will with the old inheritance method of doing it. >=C2=A0 EXECUTE 'CREATE TABLE partition_tab.' || c_table || '(check ( name = like '''|| chk_cond||''')) INHERITS (' ||TG_TABLE_NAME|| ');'; 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 tables you need beforehand. I'd recommend you do this using RANGE partitioning. For example: 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'); 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 ------------------------------------------------------------------- Seq Scan on mytable_a mytable=C2=A0 (cost=3D0.00..27.00 rows=3D7 width=3D3= 2) =C2=A0 Filter: (a =3D 'alpha'::text) (2 rows) The mytable_b is not scanned. David =20 ------=_Part_144820_1336019550.1621564574976 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Thank you. This is a great h= elp. 

But "a" have some records with alpha and num= eric. 

example :

insert in= to mytable values('alpha'),('bravo');
insert into mytable values('1lpha'),('2ravo'= );

=

=20
=20
On Thursday, May 20, 2021, 06:23:14 PM PDT, David Rowle= y <dgrowleyml@gmail.com> wrote:


On Fri, 21 May 2021 at 12:32, Nagaraj= Raj <nagaraj.sf@yahoo.com> wrote:
> I am trying 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 pe= rformance out of native partitioning than you
will with t= he 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 lock upgrade hazard here that could en= d
up causing deadlocks on INSERT.  You should just c= reate all the tables
you need beforehand.

I'd recommend you do this using RANGE partitioning. F= or example:

create table mytable (a te= xt 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.
cre= ate 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';=
                =             QUERY PLAN
----= ---------------------------------------------------------------
Seq Scan on mytable_a mytable  (cost=3D0.00..27.00 rows=3D7 wi= dth=3D32)
  Filter: (a =3D 'alpha'::text)
(2 rows)

The mytable_b is no= t scanned.

David



------=_Part_144820_1336019550.1621564574976--