From: Nagaraj Raj <nagaraj.sf@yahoo.com>
To: David Rowley <dgrowleyml@gmail.com>
Cc: Pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Partition with check constraint with "like"
Date: Fri, 21 May 2021 02:36:14 +0000 (UTC)
Message-ID: <1347719485.144821.1621564574978@mail.yahoo.com> (raw)
In-Reply-To: <CAApHDvoy=PR7SY=btN9Oiyv35RkD8ZbvuXaDaAfwreQqx7WW6w@mail.gmail.com>
References: <87840670.92362.1621557131080.ref@mail.yahoo.com>
<87840670.92362.1621557131080@mail.yahoo.com>
<CAApHDvoy=PR7SY=btN9Oiyv35RkD8ZbvuXaDaAfwreQqx7WW6w@mail.gmail.com>
Thank you. This is a great help.
But "a" have some records with alpha and numeric.
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.
David
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: nagaraj.sf@yahoo.com, dgrowleyml@gmail.com
Subject: Re: Partition with check constraint with "like"
In-Reply-To: <1347719485.144821.1621564574978@mail.yahoo.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