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
