Thread: Issue related with patitioned table:How can I quickly determine which child table my record is in,given a specific primary key value?
Issue related with patitioned table:How can I quickly determine which child table my record is in,given a specific primary key value?
From
"王旭"
Date:
Hello!
My table is described as below:
################
CREATE TABLE IF NOT EXISTS mytable
(
uuid varchar(45) NOT NULL,
symbol_id smallint NOT NULL,
...
...
PRIMARY KEY (symbol_id,uuid)
) partition by hash(symbol_id)
create table mytable_0 partition of 0 FOR VALUES WITH (MODULUS 3, REMAINDER 0);
create table mytable_1 partition of 1 FOR VALUES WITH (MODULUS 3, REMAINDER 1);
create table mytable_2 partition of 2 FOR VALUES WITH (MODULUS 3, REMAINDER 2);
(
uuid varchar(45) NOT NULL,
symbol_id smallint NOT NULL,
...
...
PRIMARY KEY (symbol_id,uuid)
) partition by hash(symbol_id)
create table mytable_0 partition of 0 FOR VALUES WITH (MODULUS 3, REMAINDER 0);
create table mytable_1 partition of 1 FOR VALUES WITH (MODULUS 3, REMAINDER 1);
create table mytable_2 partition of 2 FOR VALUES WITH (MODULUS 3, REMAINDER 2);
################
I understand that I can find the specific child table using explain.But is there any simpler way
in which I can get the name of child table via a specific symbol_id value,so that I can use it to
execute query on child table instead of on partition master table?
I tried something like this: e.g., for symbol_id 6365,
SELECT (hashint2(6365::SMALLINT)% 10)
But the result doesn't make any sense,cannot be used to determine which child table(mytable_0,mytable_1,mytable_2) my record is in.
The reason is that my frequent query doesn't need primary key(the query is on some other index),so query on a specific child table would
give me much more improvement on performance(Query would execute without loading the primary key index at all).
Is there any simple function to do this job? Can someone help me on this?
Many Thanks,
James.
Re: Issue related with patitioned table:How can I quickly determinewhich child table my record is in,given a specific primary key value?
From
Luca Ferrari
Date:
On Wed, Jul 17, 2019 at 9:19 AM 王旭 <wangxu@gu360.com> wrote: > I tried something like this: e.g., for symbol_id 6365, > SELECT (hashint2(6365::SMALLINT)% 10) shouldn't this be modulus 3 instead of 10? The problem is that record 6365 is not where you expected to be? As far as I know, there is no easy user-level way to get the route to a table, but you can juggle with the expression that defined each table and make a good guess. However, your query should give a good idea: # SELECT 'my_table_' || (hashint2(6365::smallint)% 3); ?column? ------------ my_table_2
Re: Issue related with patitioned table:How can I quickly determine which child table my record is in,given a specific primary key value?
From
"James(王旭)"
Date:
Thanks you Luca.
Yes my intention was " SELECT (hashint2(6365::SMALLINT)% 3) ", that's my mistake, thank you for pointing out.
Actually I just did a simple query in my single table:
SELECT distinct(symbol_id) FROM xxxx_0
and I got these results:
"symbol_id"
6521
1478
1964
5642
7470
1158
2429
9882
4542
5196
9178
8303
1091
9435
8133
1437
9072
From these results I can tell the route to a table is not even related with the mod function, right?
So It's hard for me to do any kind of guesses...
------------------ Original ------------------
From: "Luca Ferrari"<fluca1978@gmail.com>;
Date: Wed, Jul 17, 2019 05:13 PM
To: "王旭"<wangxu@gu360.com>;
Cc: "pgsql-general"<pgsql-general@lists.postgresql.org>;
Subject: Re: Issue related with patitioned table:How can I quickly determine which child table my record is in,given a specific primary key value?
On Wed, Jul 17, 2019 at 9:19 AM 王旭 <wangxu@gu360.com> wrote:
> I tried something like this: e.g., for symbol_id 6365,
> SELECT (hashint2(6365::SMALLINT)% 10)
shouldn't this be modulus 3 instead of 10?
The problem is that record 6365 is not where you expected to be?
As far as I know, there is no easy user-level way to get the route to
a table, but you can juggle with the expression that defined each
table and make a good guess.
However, your query should give a good idea:
# SELECT 'my_table_' || (hashint2(6365::smallint)% 3);
?column?
------------
my_table_2
> I tried something like this: e.g., for symbol_id 6365,
> SELECT (hashint2(6365::SMALLINT)% 10)
shouldn't this be modulus 3 instead of 10?
The problem is that record 6365 is not where you expected to be?
As far as I know, there is no easy user-level way to get the route to
a table, but you can juggle with the expression that defined each
table and make a good guess.
However, your query should give a good idea:
# SELECT 'my_table_' || (hashint2(6365::smallint)% 3);
?column?
------------
my_table_2
Re: Issue related with patitioned table:How can I quickly determine which child table my record is in,given a specific primary key value?
From
"James(王旭)"
Date:
Here's my PG version:
PostgreSQL 11.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
Hope this helps.
------------------ Original ------------------
From: "James(王旭)"<wangxu@gu360.com>;
Date: Wed, Jul 17, 2019 05:36 PM
To: "Luca Ferrari"<fluca1978@gmail.com>;
Cc: "pgsql-general"<pgsql-general@lists.postgresql.org>;
Subject: Re: Issue related with patitioned table:How can I quickly determine which child table my record is in,given a specific primary key value?
Thanks you Luca.
Yes my intention was " SELECT (hashint2(6365::SMALLINT)% 3) ", that's my mistake, thank you for pointing out.
Actually I just did a simple query in my single table:
SELECT distinct(symbol_id) FROM xxxx_0
and I got these results:
"symbol_id"
6521
1478
1964
5642
7470
1158
2429
9882
4542
5196
9178
8303
1091
9435
8133
1437
9072
From these results I can tell the route to a table is not even related with the mod function, right?
So It's hard for me to do any kind of guesses...
------------------ Original ------------------
From: "Luca Ferrari"<fluca1978@gmail.com>;
Date: Wed, Jul 17, 2019 05:13 PM
To: "王旭"<wangxu@gu360.com>;
Cc: "pgsql-general"<pgsql-general@lists.postgresql.org>;
Subject: Re: Issue related with patitioned table:How can I quickly determine which child table my record is in,given a specific primary key value?
On Wed, Jul 17, 2019 at 9:19 AM 王旭 <wangxu@gu360.com> wrote:
> I tried something like this: e.g., for symbol_id 6365,
> SELECT (hashint2(6365::SMALLINT)% 10)
shouldn't this be modulus 3 instead of 10?
The problem is that record 6365 is not where you expected to be?
As far as I know, there is no easy user-level way to get the route to
a table, but you can juggle with the expression that defined each
table and make a good guess.
However, your query should give a good idea:
# SELECT 'my_table_' || (hashint2(6365::smallint)% 3);
?column?
------------
my_table_2
> I tried something like this: e.g., for symbol_id 6365,
> SELECT (hashint2(6365::SMALLINT)% 10)
shouldn't this be modulus 3 instead of 10?
The problem is that record 6365 is not where you expected to be?
As far as I know, there is no easy user-level way to get the route to
a table, but you can juggle with the expression that defined each
table and make a good guess.
However, your query should give a good idea:
# SELECT 'my_table_' || (hashint2(6365::smallint)% 3);
?column?
------------
my_table_2
Re: Issue related with patitioned table:How can I quickly determinewhich child table my record is in,given a specific primary key value?
From
Luca Ferrari
Date:
On Wed, Jul 17, 2019 at 11:41 AM James(王旭) <wangxu@gu360.com> wrote:
> From these results I can tell the route to a table is not even related with the mod function, right?
> So It's hard for me to do any kind of guesses...
Because it is the wrong function.
According to \d+ on a child table and partbounds.c the function called
is satisfied_hash_partition:
testdb=# select satisfies_hash_partition('153221'::oid, 3, 0, 6521);
satisfies_hash_partition
--------------------------
t
(1 row)
testdb=# select satisfies_hash_partition('153221'::oid, 3, 1, 6521);
satisfies_hash_partition
--------------------------
f
(1 row)
The first argument is the table id (partitioned one, the root), the
second is the reminder, third is the partition table, last is your
value.
Therefore I suspect you have to iterate on your partition numbers from
0 to x to see if a value fits in that partition, and then extract the
table name from that.
Hope its clear.
Luca
Re: Issue related with patitioned table:How can I quickly determine which child table my record is in,given a specific primary key value?
From
"James(王旭)"
Date:
Hi Luca,
Yes, that's the answer,It really works!
Thanks again Luca, you actually saved my day!
James.
------------------ Original ------------------
From: "Luca Ferrari"<fluca1978@gmail.com>;
Date: Wed, Jul 17, 2019 06:49 PM
To: "James(王旭)"<wangxu@gu360.com>;
Cc: "pgsql-general"<pgsql-general@lists.postgresql.org>;
Subject: Re: Issue related with patitioned table:How can I quickly determine which child table my record is in,given a specific primary key value?
On Wed, Jul 17, 2019 at 11:41 AM James(王旭) <wangxu@gu360.com> wrote:
> From these results I can tell the route to a table is not even related with the mod function, right?
> So It's hard for me to do any kind of guesses...
Because it is the wrong function.
According to \d+ on a child table and partbounds.c the function called
is satisfied_hash_partition:
testdb=# select satisfies_hash_partition('153221'::oid, 3, 0, 6521);
satisfies_hash_partition
--------------------------
t
(1 row)
testdb=# select satisfies_hash_partition('153221'::oid, 3, 1, 6521);
satisfies_hash_partition
--------------------------
f
(1 row)
The first argument is the table id (partitioned one, the root), the
second is the reminder, third is the partition table, last is your
value.
Therefore I suspect you have to iterate on your partition numbers from
0 to x to see if a value fits in that partition, and then extract the
table name from that.
Hope its clear.
Luca
> From these results I can tell the route to a table is not even related with the mod function, right?
> So It's hard for me to do any kind of guesses...
Because it is the wrong function.
According to \d+ on a child table and partbounds.c the function called
is satisfied_hash_partition:
testdb=# select satisfies_hash_partition('153221'::oid, 3, 0, 6521);
satisfies_hash_partition
--------------------------
t
(1 row)
testdb=# select satisfies_hash_partition('153221'::oid, 3, 1, 6521);
satisfies_hash_partition
--------------------------
f
(1 row)
The first argument is the table id (partitioned one, the root), the
second is the reminder, third is the partition table, last is your
value.
Therefore I suspect you have to iterate on your partition numbers from
0 to x to see if a value fits in that partition, and then extract the
table name from that.
Hope its clear.
Luca