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-ad...@lists.postgresql.org>.
Good luck, rik. On Tue, Jun 29, 2021 at 8:28 AM Piotr Włodarczyk < piotrwlodarczy...@gmail.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 >
