Re: [EXT] FK to partitioned table

Heiko,

Feel free to ask experts you said about them ;)

On Tue, 29 Jun 2021, 3:49 pm , <heiko(dot)onnebrink(at)metronom(dot)com> wrote:

> uhps… please ignore answer below
> thought this was some internal mail .. did not see it was coming from the
> mailing list 😉
> cheers
> Heiko
>
>
>
> *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
>
>
>
> Hi,
> good question.. cannot answer straight..
>
> Here some of my bookmarks .. not sure if the question is covered there
> https://blog.dbi-services.com/postgresql-partitioning-5-partition-pruning/
>
>
> https://severalnines.com/database-blog/how-take-advantage-new-partitioning-features-postgresql-11
>
>
> 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..
>
>
> *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
>
>
>
> 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?
>
>
>
> --
>
>
>
> Pozdrawiam
>
> Piotr Włodarczyk
>
> Geschäftsanschrift/Business address: METRO-NOM GmbH, Metro-Straße 12,
> 40235 Duesseldorf, Germany
> Geschäftsführung/Management Board: Thomas Viefhaus
>
> 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 *(at)metronom(dot)com <http://metrosystems.net/>
> 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.
>
> Regarding mails from *(at)metronom(dot)com <http://metrosystems.net/>
> 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
>
> Geschäftsanschrift/Business address: METRO-NOM GmbH, Metro-Straße 12,
> 40235 Duesseldorf, Germany
> Geschäftsführung/Management Board: Thomas Viefhaus
>
> 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 *(at)metronom(dot)com <http://metrosystems.net/>
> 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.
>
> Regarding mails from *(at)metronom(dot)com <http://metrosystems.net/>
> 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
>
>