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 1lkAnW-0001fx-Cx for pgsql-performance@arkaria.postgresql.org; Fri, 21 May 2021 19:27:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lkAnV-0000Pq-79 for pgsql-performance@arkaria.postgresql.org; Fri, 21 May 2021 19:27: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 1lkAnU-0000PQ-Df for pgsql-performance@lists.postgresql.org; Fri, 21 May 2021 19:27:00 +0000 Received: from sonic305-21.consmr.mail.ne1.yahoo.com ([66.163.185.147]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1lkAnR-0008A2-3z for pgsql-performance@postgresql.org; Fri, 21 May 2021 19:26:59 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1621625215; bh=HFK57SykSBZC0ZH8SDvJuhh7E1Eu16HborN9eRUD6aU=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject:Reply-To; b=dmR0k6hnzx+zy4KvEUVCB5QIXCvGFt+DoEXYLkKP5wIg92+i3lBhgYwHyqTPtFEt1sEnsgEqgSFGXXH5xtKxCphItkhnDnkSc3uOyw3t9Hmuu9RzVftlGBWbUT09V6CIQux6w3geMVc01eyAeR+DcS7l5X0b9SpD1LYieu2AqMGzL64moO05YqY4f7ucUuKsNv3/W0aPR+QMY10VOy4Um19CCFxwLxpmxK60wa0iXnXOv96/13VcMW7EHVEOwW7x8DKzqQB0drpmTGIwUXmQJbu9BfhD0B/GqMYtMar44S/B339efGwQ4O4f8IbtjERLWMEcLd9nT7MKXfM8iqOIhg== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1621625215; bh=hOJ43mmxSqDyDFlLXnRrWBNA2siRT48ufVMpmn1NK1u=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=DzA7QH4AZraguE+Iq160GoN2xciU3AtLYwfDxTAbUJKdU694U//F+fQxMRnrFU14G3YH1sTnOWsrtJ5nJrctzZvsBvukh52qgvfEXjE34tg6pazaX+TmMPOn/3g8exQ6gETp8PxSXQn5v4BGn//SEuNG+u00dw6OFSrodVvIjqzHa5bhV2y//a77P5D01kHjaxT+6xJrFVzbbx9ya11bekWiw3lyFksfDW5QAOgqRlEFE1hS+ewrXkVjas3oHzhoqbZt9RVu1DUfeVmHXg0q5VidpWC1IUkMf2eQgsEQSn14E7X4nwrckdec+YJ1Yk46srqdwAakTid9J3b8GAZaXw== X-YMail-OSG: hRsZw9AVM1kXe26g9lbEuoHOD0D.urX8pwoJr9hj_IxGTb_UkV9POFXZjJhpc4R .qzStnV_V4xaXxeWmvL8s9pZSv1UqYqB8_JPOQ.lUJz328rA2GQh8I_ksOgi8nehPLv53EONN8kx T5hzoWCs_QjD3wVX78NGmPUZ77XWfBxJ95wexgOhntm3FK7Z1hd.eRl4gmTuxVtShLWnm2CA85LZ xsoMo8S1wi4vfVxm5z3abZESPNClbQ_ta9iHtfrVKyKtFTBiaWqleS7GKJkO_1Pf8fBNlxYtChNX wCOCAZ2vcQ6NnyhlAYZp9dJbkLhi06pztR9Lw3nBxDI5.qVoQmlG4BNA9SUBvm8vH5UOcyVQE.Uk w5dhaewbGA4OX4ietyx7a6u80mTVRhoE8h8tOB7n0BKFlQwJgYuxK7TG1xy.b5x2ez.We8Cci90U mGxnavYf3rqxjXlWZnYkc7jilmhNkBsuDjrmI5nrdqFeqANjeHCq1pEV.7FP6tu12kiKT0au5hJV vA9oYzyxAbOUQ9VXsexYE1ywa1Eux218ch.l1.L_3GV2_9lWZrS3YrBSf.IARQ4gAJ8VuSJcV_NW c2oE64z6geJPZsiJ74GEOik9NQIzMjdUQo7KKgtsVr1oa4BIFfLhAc4lvxxSbtXd47KFZ9PCTxFi x2YMIj19sYeSiSTzjVZQVmJLrfj3ifO_Zp_pmZmPZb8SwOuxMorGaxrtPTZScOIp5HlZuYHDbGtX fcPvz56woHIgbk_CygxpTrwzIL2Cj3lE9jjnIGNb2VI0yj4.D953SmAxBAQSxXpcz4Ae4.ZkmmHZ c8fqcmJW2M0gZMpm4DQHdbAfPzXcPJNy3rBB2_NyjuWU_.EulO4f0bFrbxGC.skqs8B9cLGXLFN8 eoqdV965pi0QHDqMkrHkgUnRcpjyNhG6EqMa4r6rm1Wq6XdjHpYanN1iYgY7ajleQ8Vd6xXwfeTb XeTK..fuDN9uNJiJ5U_Z9sbEk5fQPuRUafyQc7JkqeBk3JWBobR2cxmyVc4.XwE.lU1UxaLFp5Qy PwyjoZ6Kw6LQcaAAi_4gbQ7bwSWKqs.CgRZJqHYwdmtRjqybyTwavkkgsQYeq3uGA88MJQ0bZ_Vv fZtv6DwWCMwe2rs1ynB6IGhfSmJU4dehkBn9juRvoFvw_8rmhqt4YbgidaYQx32y22IDji5rWK_V 37Qd0tBMH3.kcqswJNRXVm3HMxPvl5ZdaVOgjQZluLc6L1Xbh_FRnHFUZXJ6O2m41J8jafhmZWib roTcj5obCtFdSGv6KGXXI_otvDtWkHR0Tjkm.emeaOWbeJcQg1nb2retwAqCoPiMQQfSegP3Fn1Q wK5p6tsqorM_NtcNdtMUazreLGLuFRb.UY.1_zq2H5I26zhrX9OuDNz3cgtQLnCxQ7gNRJAKKzcd LgLeu2vgd7d6aul1pzhZkis75IpS39fVShcxDuQFk021NsNgZZztJpaTYrW_LPSZasICKdRRFVez NjAbkGHurJselVlMiJ2o5z6RKnaSMkf9CYcatEYYDdckqJ3SAs5bNn9wHDEQJ.lPKGhkgVpY1n5n CUVXbaertnaYWIN7odccDWtGDp5xWYnBGs4empXbPrfjCBw5F8CxsDNJII90ZYqQ1Y3c2Q0Lbjdy fpfdnXmrozS1xBUWvANh1_MFs0hzyhGIrVhgXmvPaz52wnNAnLXrKxffht_8tL.ODd7wyBH0WsMm NF7t594nNoOO73Wg_MkTYyWPW3CqdOLzdMi82lCKtUGSoKqqBUst.vZuz1ulI4qAcvrsCe7N.G16 HNRv0T3Nu692gNt8jx5VXXtkWcPX9ESD3jgxh8rwaIrnEZb0gL1LbBiLdGW3UdDjjFQUrolQPzLH yn_hwUypoI.A4S49pV.JIUobnZGDodmUa5.3qrH3Botcy8Um9FFg7TiNt6X.AFr3UFGvPo39aMFd wSXibPNztKpSRVDYg1tD7wHBLnX98hlFByq5X7ZiF58xgSpI_kRs7PenbJ6Ybr2FN6sIyco6Jzei AyqCzpfICwvUDfMVa3.JK7UXaJP4Tz9fwzSriK98PGyuIsXSw7jLkQMENafqst3a7ylXxsrekKqG hqKD.sY4zW6nQtc9iJ5psPoYAschB0F2fb_vKHhJoYf4.12t3Yiq6HO8ROOlevGtgttbU93iGLGK xerOa7O4O1to.QDGpxFcqXKh3lAiRPS1gWmI5RajGnpfDgXeOkW36TaLEcBH05ROzjbHylna7ZdY .elHXRG8J3j_HtcXMJxqmrQtm0YiPivgsWlIpAXPDUe4GP9sxyUgC8rTpbfyYy1FMEK3k_qpsd6k U65AbKIWPgy4NJ8EwgGzJrewp6iaO2kSqtuaDnjkCLmam6GvEVosCUpo8K1RsqiBj0UMmWsvsm5r QgXE7GRv1nhjShqiE7GfxeDJaYSR0V3XOhRG71o3lurQViJN89IJz4DWxDOvjx6nHmTn4ljQxK3V BkNIRNmbkGTcW4uIVy_N5a03K8v7e8YENq8UBoaGffjNpvfne.ghKNb3ema3CoQ0nC4XNQNL8BT7 XbQXehIehsi2NOz1CZpXTTbheFH0yTJDm7pypnW9dXl1jtGqbqIdMz6ATZ7UbpAcMLyaMDy2Tvk8 gobD7EkCfxHto2WYcQbUxssrba6CaNXcAcMQqkX7hlBPPjqHCvg1MOSHDsA-- X-Sonic-MF: Received: from sonic.gate.mail.ne1.yahoo.com by sonic305.consmr.mail.ne1.yahoo.com with HTTP; Fri, 21 May 2021 19:26:55 +0000 Date: Fri, 21 May 2021 19:26:53 +0000 (UTC) From: Nagaraj Raj To: Vijaykumar Jain Cc: David Rowley , Justin Pryzby , Pgsql-performance Message-ID: <1853165594.390749.1621625213940@mail.yahoo.com> In-Reply-To: References: <87840670.92362.1621557131080.ref@mail.yahoo.com> <87840670.92362.1621557131080@mail.yahoo.com> <1347719485.144821.1621564574978@mail.yahoo.com> <20210521023836.GH3676@telsasoft.com> <81358482.168153.1621580571840@mail.yahoo.com> <2126516115.347941.1621615110621@mail.yahoo.com> Subject: Re: Partition with check constraint with "like" MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_390748_1804930497.1621625213938" X-Mailer: WebService/1.1.18368 YMailNorrin Content-Length: 15167 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_390748_1804930497.1621625213938 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable > select * from t1 where name =3D somename ?=C2=A0 =3D=3D equality match= =C2=A0 // if yes, hash partitioning may be helpful to a have reasonably bal= anced distribution yes, its an equality check, =C2=A0 On Friday, May 21, 2021, 12:08:25 PM PDT, Vijaykumar Jain wrote: =20 =20 just out=C2=A0of curiosity,what would a typical query be ? select * from t1 where name =3D somename ?=C2=A0 =3D=3D equality match=C2= =A0 // if yes, hash partitioning may be helpful to a have reasonably balanc= ed distributionorselect * from t1 where name like 'some%';=C2=A0 ----=C2=A0= what would be the distribution of rows for such queries. i mean it can ret= urn 1 row or all rows or anything in between.=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 =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 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 that may result in unbalanced partitioning.=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 =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 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 then why partition at all ? 2B rows, if i g= o with 100KB size per row. that would be around 200GB. also, queries may benefit from trigram matching.Index Columns for `LIKE` in= PostgreSQL | Niall Burkley's Developer Blog =C2=A0 On Fri, 21 May 2021 at 22:08, Nagaraj Raj wrote: Hi David, Hi, I am trying to create partitions on the table which have around 2BIL record= s and users will always look for the "name", its not possible to create a p= artition with a list, so we are trying to create a partition-based first le= tter of the name column. name column has a combination of alpha numeric val= ues. > postgres=3D# select chr(ascii('z')+1) ; > chr > ----- > { > (1 row) I tried as below, I'm able to create a partition table for 'Z', but it's no= t identifying partition table.=C2=A0 postgres=3D# select chr(ascii('Z')+1) ; chr ----- [ (1 row) create table mytable_z of mytable=C2=A0 for values from ('Z') to ('Z[');CR= EATE TABLE=C2=A0 insert into mytable=C2=A0 values(4,'ZAR83NB'); ERROR: no partition of relation "mytable" found for rowDETAIL: Partition ke= y of the failing row contains (name) =3D (ZAR83NB).SQL state: 23514 On Friday, May 21, 2021, 01:24:13 AM PDT, David Rowley wrote: =20 =20 On Fri, 21 May 2021 at 19:02, Nagaraj Raj wrote: > then what would be the range of Z > FROM (Z) to (?) ; postgres=3D# select chr(ascii('z')+1) ; chr ----- { (1 row) > same way for 9 postgres=3D# select chr(ascii('9')+1) ; chr ----- : (1 row) https://en.wikipedia.org/wiki/ASCII You can also use MINVALUE and MAXVALUE to mean unbounded at either end of the range. But is there a particular need that you want to partition this way? It seems like it might be a bit painful to maintain, especially if you're not limiting yourself to ASCII or ANSI characters. You might want to consider HASH partitioning if you're just looking for a way to keep your tables and indexes to a more manageable size. You've not really mentioned your use case here, so it's hard to give any advice. There are more details about partitioning in https://www.postgresql.org/docs/current/ddl-partitioning.html David =20 --=20 Thanks,VijayMumbai, India =20 ------=_Part_390748_1804930497.1621625213938 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
> select * from t1 = where name =3D somename ?  =3D=3D equality match  // if yes, hash= partitioning may be helpful to a have reasonably balanced distribution
yes, its an equa= lity check,


 
=20
=20
On Friday, May 21, 2021, 12:08:25 PM PDT, Vijaykumar Ja= in <vijaykumarjain.github@gmail.com> wrote:


just out of curiosity,
what= would a typical query be ?

select = * from t1 where name =3D somename ?  =3D=3D equality match  // if= yes, hash partitioning may be helpful to a have reasonably balanced distri= bution
or
select * from t1 where name like 'some%';&nbs= p; ----  what would be the distribution of rows for such queries. i me= an it can return 1 row or all rows or anything in between.
 =                     &nbs= p;                     &n= bsp;                     =           that may result in unbalanced partitioni= ng.
                 = ;                     &nb= sp;                     &= nbsp;               then why partition a= t all ? 2B rows, if i go with 100KB size per row. that would be around 200G= B.

also, queries may benefit from t= rigram matching.


 



On Fri= , 21 May 2021 at 22:08, Nagaraj Raj <nagaraj.sf@yahoo.com> wrote:=
Hi David,

Hi,

=
I am trying to create partitions on the table which have aro= und 2BIL records and users will always look for the "name", its not possibl= e to create a partition with a list, so we are trying to create a partition= -based first letter of the name column. name column has a combination of al= pha numeric values.



> postgres=3D# select = chr(ascii('z')+1) ;
> chr
&= gt; -----
> = {
> (1 row)<= /span>

I tried as below, I'm able to create a partitio= n table for 'Z', but it's not identifying partition table. 

pos= tgres=3D# select chr(ascii('Z')+1) ;
chr
-----
[
(= 1 row)

create table mytable_z of mytable&nb= sp; for values from ('Z') to ('Z[');
CREATE TABLE&nbs= p;

in= sert into mytable  values(4,'ZAR83N= B');

ERROR: no partition o= f relation "mytable" found for row DETAIL: Partition key of the failing row contains (name) =3D (ZAR83NB). SQL state: 23514





=20
=20
On Friday, May 21, 2021, 01:24:13 AM PDT, David Rowley = <dgrowleyml@gmail.com> wrote:


On Fri, 21 May 2021 at 19:02, Nagaraj= Raj <nagaraj.sf@yahoo.com> wrote:
> then wh= at would be the range of Z
> FROM (Z) to (?) ;

postgres=3D# select chr(ascii('z')+1) ;
chr
-----
{
(1 row)


> = same way for 9

postgres=3D# select chr= (ascii('9')+1) ;
chr
-----
:
(1 row)

https://en.wikipedia.org/wiki/ASCI= I

You can also use MINVALUE and MA= XVALUE to mean unbounded at either end
of the range.

But is there a particular need that you wa= nt to partition this way? It
seems like it might be a bit= painful to maintain, especially if you're
not limiting y= ourself to ASCII or ANSI characters.

Y= ou might want to consider HASH partitioning if you're just looking
for a way to keep your tables and indexes to a more manageable si= ze.
You've not really mentioned your use case here, so it= 's hard to give
any advice.

There are more details about partitioning in
https://= www.postgresql.org/docs/current/ddl-partitioning.html
<= br clear=3D"none">
David




--
T= hanks,
Vijay
Mumbai, India
------=_Part_390748_1804930497.1621625213938--