Constraint Exclusion not working on partitioned table

Question

I have following setup (simplified excerpt):

CREATE TABLE my_table (
  ID VARCHAR(255) NOT NULL,
  TENANT_ID CHAR(36) NOT NULL,
  PRIMARY KEY (id)
);

-- creates a int hash of a string (> 0)
CREATE OR REPLACE FUNCTION h_int(text) RETURNS int as $$
  SELECT @('x'||substr(md5($1),1,8))::bit(32)::int;
$$ language sql;

-- uses a numeric hash value to select a partition
CREATE OR REPLACE FUNCTION select_partition(text, int) RETURNS int as $$
  SELECT h_int($1) % $2;
$$ language sql;

CREATE TABLE IF NOT EXISTS part_my_table_00 (
  CONSTRAINT pk_part_00 PRIMARY KEY (ID),
  CONSTRAINT ck_part_00 CHECK (select_partition(TENANT_ID, 2) = 0)
) INHERITS (my_table);

CREATE INDEX idx_tenant_ids_00 ON my_table (TENANT_ID);

CREATE TABLE IF NOT EXISTS part_my_table_01 (
  CONSTRAINT pk_part_01 PRIMARY KEY (ID),
  CONSTRAINT ck_part_01 CHECK (select_partition(TENANT_ID, 2) = 1)
) INHERITS (my_table);

CREATE INDEX idx_tenant_ids_01 ON my_table (TENANT_ID);

CREATE OR REPLACE FUNCTION fn_insert() RETURNS TRIGGER AS $$
  declare
    selectedPartition int;
    tableName text := 'part_my_table_0';
    queryString text;
  BEGIN
    selectedPartition := select_partition(NEW.TENANT_ID, 2);
    tableName := tableName||selectedPartition;
    queryString := 'INSERT INTO '||tableName||' SELECT($1).*';
    EXECUTE queryString USING NEW;
    RETURN NULL;
  END;
  $$ LANGUAGE plpgsql;

CREATE TRIGGER tr_insert BEFORE INSERT ON my_table
FOR EACH ROW EXECUTE PROCEDURE fn_insert();

SET constraint_exclusion = on;

Now inserts do work well. My issue is that constraint exclusion does not seem to work for selects:

SELECT * FROM my_table WHERE TENANT_ID='anyVal';

Explaining this query returns following:

postgres=# EXPLAIN SELECT * FROM my_table WHERE TENANT_ID='anyVal';
                                               QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..81.53 rows=11 width=6401)
   ->  Seq Scan on my_table  (cost=0.00..0.00 rows=1 width=6401)
         Filter: (tenant_id = 'bla'::bpchar)
   ->  Index Scan using idx_tenant_ids_00 on part_my_table_00 (cost=0.14..8.15 rows=1 width=6401)
         Index Cond: (tenant_id = 'bla'::bpchar)
   ->  Index Scan using idx_tenant_ids_01 on part_my_table_01 (cost=0.14..8.15 rows=1 width=6401)
         Index Cond: (tenant_id = 'bla'::bpchar)

I would have expected that only one of the partitions would have been scanned. Anybody able to help me?

Thanks in advance!

Answer 1

You have to add a WHERE condition that matches the partitioning constraint:

SELECT * FROM my_table
   WHERE TENANT_ID='anyVal'
     AND select_partition(TENANT_ID, 2) = select_partition('anyVal', 2);

Moreover, I think that you should mark h_int and select_partition as IMMUTABLE. They are (and should be), and it is a good idea to tell the database. But that is unrelated to your problem.