From: | 王旭 <wangxu(at)gu360(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Issue related with patitioned table:How can I quickly determine which child table my record is in,given a specific primary key value? |
Date: | 2019-07-17 07:18:41 |
Message-ID: | tencent_267C505771F9162A6E330836@qq.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Dirk Mika | 2019-07-17 07:26:13 | Re: How to run a task continuously in the background |
Previous Message | Ian Barwick | 2019-07-17 03:51:18 | Re: [EXT EMAIL] Re: First Time Starting Up PostgreSQL and Having Problems |