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);
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.