FK to partitioned table

[Piotr Włodarczyk <piotrwlodarczyk89@xxxxxxxxx>]

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