Re: Partition with check constraint with "like"

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



> select * from t1 where name = somename ?  == equality match  // if yes, hash partitioning may be helpful to a have reasonably balanced distribution
yes, its an equality check,


 
On Friday, May 21, 2021, 12:08:25 PM PDT, Vijaykumar Jain <vijaykumarjain.github@xxxxxxxxx> wrote:


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

select * from t1 where name = somename ?  == equality match  // if yes, hash partitioning may be helpful to a have reasonably balanced distribution
or
select * from t1 where name like 'some%';  ----  what would be the distribution of rows for such queries. i mean it can return 1 row or all rows or anything in between.
                                                                            that may result in unbalanced partitioning.
                                                                            then why partition at all ? 2B rows, if i go with 100KB size per row. that would be around 200GB.

also, queries may benefit from trigram matching.


 



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

Hi,

I am trying to create partitions on the table which have around 2BIL records and users will always look for the "name", its not possible 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 alpha numeric values.



> postgres=# 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 not identifying partition table. 


postgres=# select chr(ascii('Z')+1) ;
chr
-----
[
(1 row)

create table mytable_z of mytable  for values from ('Z') to ('Z[');
CREATE TABLE 

insert into mytable  values(4,'ZAR83NB');

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





On Friday, May 21, 2021, 01:24:13 AM PDT, David Rowley <dgrowleyml@xxxxxxxxx> wrote:


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

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


> same way for 9

postgres=# 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




--
Thanks,
Vijay
Mumbai, India

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux