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