Issue related with patitioned table:How can I quickly determine which child table my record is in,given a specific primary key value?
Hello! My table is described as below: CREATE TABLE IF NOT EXISTS mytable ( uuid varchar(45) NOT NULL, symbol_idsmallint 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 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 _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"; Date: Wed, Jul 17, 2019 05:13 PM To: "王旭"; Cc: "pgsql-general"; 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 王旭 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?
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(王旭)"; Date: Wed, Jul 17, 2019 05:36 PM To: "Luca Ferrari"; Cc: "pgsql-general"; 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 _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"; Date: Wed, Jul 17, 2019 05:13 PM To: "王旭"; Cc: "pgsql-general"; 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 王旭 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?
Hi Luca,
Yes, that's the answer,It really works!
Thanks again Luca, you actually saved my day!
James.
-- Original --
From: "Luca Ferrari";
Date: Wed, Jul 17, 2019 06:49 PM
To: "James(王旭)";
Cc: "pgsql-general";
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(王旭) 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
How to gracefully keep my specific index in memory ?
Hello: As the title,How to keep a specific index in memory gracefully? After some statistical query, I can determine that not all indexes can be fit into memory, but one of the most frequently used indexes(say idx_xyz) can be definitely fit into memory(specifically ,[the size of idx_xyz]=20% x [memory size]). I know there's pgprewarm, but I feel with pgprewarm I can't keep things under control, e.g. no realtime monitor, being squeezed out of memory ,.etc. Is it possible that I can simply do something like "select idx_xyz into xxx" and keep the specific index in memory forever? Best regards, James
Re: How to gracefully keep my specific index in memory ?
Thanks Laurenz Albe for reply. Unfortunately this index is only frequently used during a certain period of time(such as 9:30am - 12:00am) .I usually encounter slow load of this index during some other time. Then it seems to me the only answer for this case would be the pgprewarm and pg_cron ? -- Original -- From: "Laurenz Albe"; Date: Mon, Aug 12, 2019 08:24 PM To: "James(王旭)"; "pgsql-general"; Subject: Re: How to gracefully keep my specific index in memory ? James(王旭) wrote: > As the title,How to keep a specific index in memory gracefully? > > After some statistical query, I can determine that not all indexes can be fit > into memory, > but one of the most frequently used indexes(say idx_xyz) can be definitely > fit into > memory(specifically ,[the size of idx_xyz]=20% x [memory size]). > > I know there's pgprewarm, but I feel with pgprewarm I can't keep things under > control, e.g. no realtime monitor, being squeezed out of memory ,.etc. > > Is it possible that I can simply do something like "select idx_xyz into xxx" > and keep the specific index in memory forever? If the indexes are frequently used, they should remain cached anyway. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
How should I specify work_mem/max_worker_processes if I want to do big queries now and then?
Hello, I am doing a query to fetch about 1000 records in one time. But the query seems very slow, like "mission impossible". I am very confident that these records should be fit into my shared_buffers settings(20G), and my query is totally on my index, which is this big:(19M x 100 partitions), this index size can also be put into shared_buffers easily.(actually I even made a new partial index which is smaller and delete the bigger old index) This kind of situation makes me very disappointed.How can I make my queries much faster if my data grows more than 1000 in one partition? I am using pg11.6. Many thanks, James
Re: How should I specify work_mem/max_worker_processes if I want to do big queries now and then?
Hello Laurenz, Thanks Laurenz for the quick reply, I will supply these information later. James
Re: How should I specify work_mem/max_worker_processes if I want to do big queries now and then?
Thanks Thomas for this information, I will try that and get back here. James -- Original -- From: "Thomas Kellerer"
Re: How should I specify work_mem/max_worker_processes if I want to do big queries now and then?
Thanks Imre, this is a very important comment, 128 bits is much smaller than 45*8+2=362. Very glad to know that, thank you very much! James -- Original -- From: "Imre Samu"https://www.postgresql.org/docs/11/datatype-uuid.html "UUID would be the fastest because its 128 bits -> 16 bytes and comparisons are done numerically." https://stackoverflow.com/questions/32189129/performance-difference-between-uuid-char-and-varchar-in-postgresql-table The smaller size can be important for your index size ! : "quotes_pkey PRIMARY KEY (symbol_id, uuid);" Imre
How to prevent POSTGRES killing linux system from accepting too much inserts?
Hello,I encountered into this kernel message, and I cannot login into the Linux system anymore: Dec 17 23:01:50 hq-pg kernel: sh (6563): drop_caches: 1Dec 17 23:02:30 hq-pg kernel: INFO: task sync:6573 blocked for more than 120 seconds.Dec 17 23:02:30 hq-pg kernel: "echo 0 > /proc/sys/kernel/hung_task_timeout_secs" disables this message.Dec 17 23:02:30 hq-pg kernel: sync D 965ebabd1040 0 6573 6572 0x0080Dec 17 23:02:30 hq-pg kernel: Call Trace:Dec 17 23:02:30 hq-pg kernel: [
What to do with tablespaces when upgrading to pg13 from pg1X?
Hello, I was planning to upgrade from 12 to 13 using this command: /usr/pgsql-13/bin/pg_upgrade -b /usr/pgsql-12/bin/ -B /usr/pgsql-13/bin/ -d /data/pg/ -D /pg/pgdata_13/ --jobs=10 And I got this output: Checking for presence of required libraries ok Checking database user is the install user ok Checking for prepared transactions ok Checking for new cluster tablespace directories new cluster tablespace directory already exists: "/data/tsdb/metas/PG_13_202007201" Failure, exiting I think it's because /data/tsdb was my tablespace dir which was out of the old main data directory(/data/pg/). So what should I do with old tablespaces when upgrading ? Thanks a lot !
Re: What to do with tablespaces when upgrading to pg13 from pg1X?
You mean PG_13_202007201 was created by PG 13 ? No, there isn't any other clusters, maybe it was created by my previous failed attempts of upgrading. So it seems it should be ok that I issue a command like this find . -name "PG_13*"|xargs rm -rf and then do upgrade again? There should be a subdirectory under your tablespace directory for every major version, e.g., PG_13_202007201. I have no idea why your _new_ version already has a directory there. Do you have a second cluster on the machine that is using that tablespace directory for PG 13? -- Bruce Momjian https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
Re: What to do with tablespaces when upgrading to pg13 from pg1X?
Got you, thanks a lot Bruce! -- Original -- From: "Bruce Momjian"https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
couldn't drop subscription after upgrading via logical replication.
Hello! I upgraded my postgres from 11 to 13 using logical replication(publication in PG-11 and subscription in PG-13 of course). Now I encountered this problem, I cannot drop the subscription in PG-13: drop subscription sub_upgrade_to_13; ERROR: could not drop the replication slot "sub_upgrade_to_13" on publisher DETAIL: The error was: ERROR: replication slot "sub_upgrade_to_13" does not exist The thing is that I already stoped my PG-11, there's no way I drop the replication slot "sub_upgrade_to_13" on publisher, is there anything I can do to solve it? Many thanks!
