Re: FK to partitioned table

Piotr,

Thanks for the question. This is the pgAdmin4 (a GUI for managing
PostgreSQL databases) list. You might have better luck on the postgres
list Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org>.

Good luck,

rik.

On Tue, Jun 29, 2021 at 8:28 AM Piotr Włodarczyk <
piotrwlodarczyk89(at)gmail(dot)com> wrote:

> 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
>