From: Justin Pryzby <pryzby@telsasoft.com>
To: Nagaraj Raj <nagaraj.sf@yahoo.com>
Cc: David Rowley <dgrowleyml@gmail.com>
Cc: Pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Partition with check constraint with "like"
Date: Thu, 20 May 2021 21:38:36 -0500
Message-ID: <20210521023836.GH3676@telsasoft.com> (raw)
In-Reply-To: <1347719485.144821.1621564574978@mail.yahoo.com>
References: <87840670.92362.1621557131080.ref@mail.yahoo.com>
<87840670.92362.1621557131080@mail.yahoo.com>
<CAApHDvoy=PR7SY=btN9Oiyv35RkD8ZbvuXaDaAfwreQqx7WW6w@mail.gmail.com>
<1347719485.144821.1621564574978@mail.yahoo.com>
On Fri, May 21, 2021 at 02:36:14AM +0000, Nagaraj Raj wrote:
> Thank you. This is a great help.
> But "a" have some records with alpha and numeric.
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');
>
>
> On Thursday, May 20, 2021, 06:23:14 PM PDT, David Rowley <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 performance out 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 lock upgrade hazard here that could end
> up causing deadlocks on INSERT. 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 = 'alpha';
> QUERY PLAN
> -------------------------------------------------------------------
> Seq Scan on mytable_a mytable (cost=0.00..27.00 rows=7 width=32)
> Filter: (a = 'alpha'::text)
> (2 rows)
>
> The mytable_b is not scanned.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, nagaraj.sf@yahoo.com, dgrowleyml@gmail.com
Subject: Re: Partition with check constraint with "like"
In-Reply-To: <20210521023836.GH3676@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox