Quick Links

Re: [EXT] FK to partitioned table

From:	<heiko(dot)onnebrink(at)metronom(dot)com>
To:	<piotrwlodarczyk89(at)gmail(dot)com>
Cc:	<pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject:	Re: [EXT] FK to partitioned table
Date:	2021-06-29 13:49:00
Message-ID:	79D5628F-A11D-4687-BA4F-60E571E9E789@metronom.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-admin

<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style></style>
</head>
<body lang="en-DE" link="#0563C1" vlink="purple" style="word-wrap:break-word">
<div class="WordSection1">
uhps… please ignore answer below
 
thought this was some internal mail .. did not see it was coming from the mailing list
😉 
cheers 
Heiko 
 
<o:p></o:p>
<o:p> </o:p>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm">
From: "heiko(dot)onnebrink(at)metronom(dot)com" <heiko(dot)onnebrink(at)metronom(dot)com> 
Date: Tuesday, 29. June 2021 at 15:45 
To: "piotrwlodarczyk89(at)gmail(dot)com" <piotrwlodarczyk89(at)gmail(dot)com>, "pgsql-admin(at)lists(dot)postgresql(dot)org" <pgsql-admin(at)lists(dot)postgresql(dot)org> 
Subject: Re: [EXT] FK to partitioned table<o:p></o:p>
</div>
<div>
<o:p> </o:p>
</div>
Hi, 
good question.. cannot answer straight.. 
 
Here some of my bookmarks .. not sure if the question is covered there 
<a href="https://blog.dbi-services.com/postgresql-partitioning-5-partition-pruning/">https://blog.dbi-services.com/postgresql-partitioning-5-partition-pruning/</a><o:p></o:p>
<a href="https://severalnines.com/database-blog/how-take-advantage-new-partitioning-features-postgresql-11">https://severalnines.com/database-blog/how-take-advantage-new-partitioning-features-postgresql-11</a><o:p></o:p>
 
If you do not find an answer I could connect you with some PostgreSQL experts from our Cybertec support company.. they could try to get answer from the PostgreSQL source code as last resort
😉 
 
cheers 
Heiko 
 
P.S. see that my bookmarks seem to be also a bot old.. suggest to do a fresh google for PostgreSQL 13 behaviour.. likely that things changed again as there was much done on partitioning in PG12 &
13.. specially partition pruning was heavily updated.. 
 
 
<o:p></o:p>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm">
From: Piotr Włodarczyk <piotrwlodarczyk89(at)gmail(dot)com> 
Date: Tuesday, 29. June 2021 at 14:42 
To: "pgsql-admin(at)lists(dot)postgresql(dot)org" <pgsql-admin(at)lists(dot)postgresql(dot)org> 
Subject: [EXT] FK to partitioned table<o:p></o:p>
</div>
<div>
 <o:p></o:p>
</div>
<div>
Hello geeks, 
 
I have a question I can't find any answer. 
 
First we need some basic structure. So it is: 
 
 
  CREATE SCHEMA parts 
      AUTHORIZATION postgres; 
 
  DROP TABLE IF EXISTS parts.main; 
 
  CREATE TABLE IF NOT EXISTS parts.main 
  ( 
      id bigserial NOT NULL, 
      id_ext bigint NOT NULL, 
      added timestamp without time zone, 
      CONSTRAINT pk PRIMARY KEY (id, id_ext) 
  ) PARTITION BY hash (id_ext); 
 
  ALTER TABLE parts.main 
      OWNER to postgres; 
 
Then we create a partitions: 
 
  CREATE TABLE IF NOT EXISTS parts.main_hash0 PARTITION OF parts.main FOR VALUES WITH (MODULUS 10, REMAINDER 0); 
  CREATE TABLE IF NOT EXISTS parts.main_hash1 PARTITION OF parts.main FOR VALUES WITH (MODULUS 10, REMAINDER 1); 
  CREATE TABLE IF NOT EXISTS parts.main_hash2 PARTITION OF parts.main FOR VALUES WITH (MODULUS 10, REMAINDER 2); 
  CREATE TABLE IF NOT EXISTS parts.main_hash3 PARTITION OF parts.main FOR VALUES WITH (MODULUS 10, REMAINDER 3); 
  CREATE TABLE IF NOT EXISTS parts.main_hash4 PARTITION OF parts.main FOR VALUES WITH (MODULUS 10, REMAINDER 4); 
  CREATE TABLE IF NOT EXISTS parts.main_hash5 PARTITION OF parts.main FOR VALUES WITH (MODULUS 10, REMAINDER 5); 
  CREATE TABLE IF NOT EXISTS parts.main_hash6 PARTITION OF parts.main FOR VALUES WITH (MODULUS 10, REMAINDER 6); 
  CREATE TABLE IF NOT EXISTS parts.main_hash7 PARTITION OF parts.main FOR VALUES WITH (MODULUS 10, REMAINDER 7); 
  CREATE TABLE IF NOT EXISTS parts.main_hash8 PARTITION OF parts.main FOR VALUES WITH (MODULUS 10, REMAINDER 8); 
  CREATE TABLE IF NOT EXISTS parts.main_hash9 PARTITION OF parts.main FOR VALUES WITH (MODULUS 10, REMAINDER 9); 
 
And finally one table connected by fk to parts.main: 
 
  -- Table: parts.main_additional 
 
  -- DROP TABLE parts.main_additional; 
 
  CREATE TABLE IF NOT EXISTS parts.main_additional 
  ( 
      id bigserial, 
      id_main bigint NOT NULL, 
      id_ext bigint, 
      CONSTRAINT main_additional_pkey PRIMARY KEY (id), 
      CONSTRAINT fk_1 FOREIGN KEY (id_ext, id_main) 
 REFERENCES parts.main (id_ext, id) MATCH SIMPLE 
 ON UPDATE NO ACTION 
 ON DELETE NO ACTION 
  ) 
 
  TABLESPACE pg_default; 
 
  ALTER TABLE parts.main_additional 
      OWNER to postgres; 
 
 
So we have partitioned table parts.main partitioned by hash of id_ext and having PK (id, id_ext). 
 
We have parts.main_addidtional table with some data related to parts.main. As you can see we have FK fk_1
 
 
  (...) 
       CONSTRAINT fk_1 FOREIGN KEY (id_ext, id_main) 
 REFERENCES parts.main (id_ext, id) MATCH SIMPLE 
 ON UPDATE NO ACTION 
 ON DELETE NO ACTION 
  (...) 
 
on parts.main_addidtional table. Because of parts.main is partitioned finnaly postgres create fk_1 as follows: 
 
  (...) 
  CONSTRAINT fk_1 FOREIGN KEY (id_ext, id_main) 
        REFERENCES parts.main (id_ext, id) MATCH SIMPLE 
        ON UPDATE NO ACTION 
        ON DELETE NO ACTION, 
    CONSTRAINT main_additional_id_ext_id_main_fkey FOREIGN KEY (id_ext, id_main) 
        REFERENCES parts.main_hash0 (id_ext, id) MATCH SIMPLE 
        ON UPDATE NO ACTION 
        ON DELETE NO ACTION, 
    CONSTRAINT main_additional_id_ext_id_main_fkey1 FOREIGN KEY (id_ext, id_main) 
        REFERENCES parts.main_hash1 (id_ext, id) MATCH SIMPLE 
        ON UPDATE NO ACTION 
        ON DELETE NO ACTION, 
    CONSTRAINT main_additional_id_ext_id_main_fkey2 FOREIGN KEY (id_ext, id_main) 
        REFERENCES parts.main_hash2 (id_ext, id) MATCH SIMPLE 
        ON UPDATE NO ACTION 
        ON DELETE NO ACTION, 
    CONSTRAINT main_additional_id_ext_id_main_fkey3 FOREIGN KEY (id_ext, id_main) 
        REFERENCES parts.main_hash3 (id_ext, id) MATCH SIMPLE 
        ON UPDATE NO ACTION 
        ON DELETE NO ACTION, 
    CONSTRAINT main_additional_id_ext_id_main_fkey4 FOREIGN KEY (id_ext, id_main) 
        REFERENCES parts.main_hash4 (id_ext, id) MATCH SIMPLE 
        ON UPDATE NO ACTION 
        ON DELETE NO ACTION, 
    CONSTRAINT main_additional_id_ext_id_main_fkey5 FOREIGN KEY (id_ext, id_main) 
        REFERENCES parts.main_hash5 (id_ext, id) MATCH SIMPLE 
        ON UPDATE NO ACTION 
        ON DELETE NO ACTION, 
    CONSTRAINT main_additional_id_ext_id_main_fkey6 FOREIGN KEY (id_ext, id_main) 
        REFERENCES parts.main_hash6 (id_ext, id) MATCH SIMPLE 
        ON UPDATE NO ACTION 
        ON DELETE NO ACTION, 
    CONSTRAINT main_additional_id_ext_id_main_fkey7 FOREIGN KEY (id_ext, id_main) 
        REFERENCES parts.main_hash7 (id_ext, id) MATCH SIMPLE 
        ON UPDATE NO ACTION 
        ON DELETE NO ACTION, 
    CONSTRAINT main_additional_id_ext_id_main_fkey8 FOREIGN KEY (id_ext, id_main) 
        REFERENCES parts.main_hash8 (id_ext, id) MATCH SIMPLE 
        ON UPDATE NO ACTION 
        ON DELETE NO ACTION, 
    CONSTRAINT main_additional_id_ext_id_main_fkey9 FOREIGN KEY (id_ext, id_main) 
        REFERENCES parts.main_hash9 (id_ext, id) MATCH SIMPLE 
        ON UPDATE NO ACTION 
        ON DELETE NO ACTION 
  (...) 
 
so one fk to each partition. Now I'm thinking about which fk is used when fk_1 is using? All of them or postgres can "prune" them?
 
 
I couldn't find answer in explain analyze: 
 
  explain analyze 
  update parts.main_additional 
  set id_main = 15 , id_ext = 14 
  where id = 4 
 
because of result 
 
  "Update on main_additional  (cost=0.29..8.30 rows=1 width=30) (actual time=0.106..0.107 rows=0 loops=1)" 
  "  ->  Index Scan using main_additional_pkey on main_additional  (cost=0.29..8.30 rows=1 width=30) (actual time=0.030..0.047 rows=1 loops=1)" 
  "        Index Cond: (id = 4)" 
  "Planning Time: 0.089 ms" 
  "Trigger for constraint fk_1: time=32.158 calls=1" 
  "Execution Time: 32.293 ms" 
 
tels only about using fk_1 onec. I've inserted one row so calls number is 1 or it's 1 because of 1 insert and using only one fk (for exmaple main_additional_id_ext_id_main_fkey8)? 
 
In another words: is postgres using all of fk extending fk_1 (main_additional_id_ext_id_main_fkey, main_additional_id_ext_id_main_fkey1 ... main_additional_id_ext_id_main_fkey9) or can select right one? 
 
 
 
-- <o:p></o:p>
<div>
 <o:p></o:p>
<div>
Pozdrawiam<o:p></o:p>
</div>
<div>
Piotr Włodarczyk<o:p></o:p>
</div>
</div>
</div>
Geschäftsanschrift/Business address: METRO-NOM GmbH, Metro-Straße 12, 40235 Duesseldorf, Germany 
Geschäftsführung/Management Board: Thomas Viefhaus<o:p></o:p>
Sitz Düsseldorf, Amtsgericht Düsseldorf, HRB 18232/Registered Office Düsseldorf, Commercial Register of the Düsseldorf Local Court, HRB 18232<o:p></o:p>
Betreffend Mails von *@<a href="http://metrosystems.net/" target="_blank">metronom.com</a> 
Die in dieser E-Mail enthaltenen Nachrichten und Anhänge sind ausschließlich für den bezeichneten Adressaten bestimmt. Sie können rechtlich geschützte, vertrauliche Informationen enthalten. Falls Sie nicht der bezeichnete Empfänger oder zum Empfang dieser E-Mail
nicht berechtigt sind, ist die Verwendung, Vervielfältigung oder Weitergabe der Nachrichten und Anhänge untersagt. Falls Sie diese E-Mail irrtümlich erhalten haben, informieren Sie bitte unverzüglich den Absender und vernichten Sie die E-Mail.<o:p></o:p>
Regarding mails from *@<a href="http://metrosystems.net/" target="_blank">metronom.com</a> 
This e-mail message and any attachment are intended exclusively for the named addressee. They may contain confidential information which may also be protected by professional secrecy. Unless you are the named addressee (or authorised to receive for the addressee)
you may not copy or use this message or any attachment or disclose the contents to anyone else. If this e-mail was<o:p></o:p>
</div>
Geschäftsanschrift/Business
address: METRO-NOM GmbH, Metro-Straße 12, 40235 Duesseldorf, Germany Geschäftsführung/Management Board: Thomas Viefhaus<o:p></o:p>Sitz Düsseldorf, Amtsgericht Düsseldorf, HRB 18232/Registered Office
Düsseldorf, Commercial Register of the Düsseldorf Local Court, HRB 18232

Betreffend
Mails von *@<a href="http://metrosystems.net/" target="_blank">metronom.com</a> 
Die in dieser E-Mail enthaltenen Nachrichten und Anhänge sind ausschließlich
für den bezeichneten Adressaten bestimmt. Sie können rechtlich geschützte,
vertrauliche Informationen enthalten. Falls Sie nicht der bezeichnete Empfänger
oder zum Empfang dieser E-Mail nicht berechtigt sind, ist die Verwendung,
Vervielfältigung oder Weitergabe der Nachrichten und Anhänge untersagt. Falls
Sie diese E-Mail irrtümlich erhalten haben, informieren Sie bitte unverzüglich
den Absender und vernichten Sie die E-Mail.<o:p></o:p>

Regarding mails from *@<a href="http://metrosystems.net/" target="_blank">metronom.com</a> 
This e-mail message and any attachment are intended exclusively for the named
addressee. They may contain confidential information which may also be protected
by professional secrecy. Unless you are the named addressee (or authorised to
receive for the addressee) you may not copy or use this message or any
attachment or disclose the contents to anyone else. If this e-mail was</body>
</html>

Attachment	Content-Type	Size
unknown_filename	text/html	17.9 KB

In response to

Re: [EXT] FK to partitioned table at 2021-06-29 13:44:47 from heiko.onnebrink

Responses

Re: [EXT] FK to partitioned table at 2021-06-30 11:18:46 from Piotr Włodarczyk

Browse pgsql-admin by date

	From	Date	Subject
Next Message	Piotr Włodarczyk	2021-06-30 11:18:46	Re: [EXT] FK to partitioned table
Previous Message	heiko.onnebrink	2021-06-29 13:44:47	Re: [EXT] FK to partitioned table