Constraint Exclusion when it fails to work

What is Constraint Exclusion?

Constraint Exclusion is a feature introduced in PostgreSQL 8.1 which is used in conjunction with Table Inheritance to implement table partitioning strategies. The basic idea is you put check constraints on tables to limit what kind of data can be inserted into it. Constraint Exclusion will then in theory skip over tables whose check constraints guarantee there is no way for it to satisfy the condition of a query.

Constraint Exclusion is a great thing, but it has some limitations and quirks one needs to be aware of that in some cases will prevent it from kicking in. Below are a couple of reasons why it sometimes doesn't work.

How many ways can constraint exclusion fail - let us count the ways

How do you know that Constraint Exclusion is actually working?

You can tell that constraint exclusion is or is not working by looking at the explain plan. The PgAdmin III Visual Explain plan, we find even easier to detect this since its easy to spot tables that shouldn't be there by looking at the pretty visual explain plan. We covered this in Reading PgAdmin Graphical Explain Plans.

For completeness and ease of study below i is a simple exercise along with PgAdmin visual explain plan diagrams.

ALTER DATABASE playfield SET constraint_exclusion=on;

  item_id serial NOT NULL,
  item_name character varying(50) NOT NULL,
  CONSTRAINT pk_products PRIMARY KEY (item_id),
  CONSTRAINT uidx_products_item_name UNIQUE (item_name)

CREATE TABLE products_a_j
  CONSTRAINT pk_products_a_j PRIMARY KEY (item_id),
  CONSTRAINT uidx_products_a_j_item_name UNIQUE (item_name),
  CONSTRAINT chk_in_range_item_name CHECK (item_name >= 'A' AND item_name < 'K')
INHERITS (products);

CREATE TABLE products_k_z
  CONSTRAINT pk_products_k_z PRIMARY KEY (item_id),
  CONSTRAINT uidx_products_k_z_item_name UNIQUE (item_name),
  CONSTRAINT chk_in_range_item_name CHECK (item_name >= 'K')
INHERITS (products);

--this is 8.2+ multi-insert syntax
INSERT INTO products_a_j(item_name)

INSERT INTO products_k_z(item_name)

---Test NO Constraint exclusion happening --
SELECT * FROM products
WHERE item_name LIKE 'A%';

As we see in the below diagram - both child tables are being checked although there is no way the k_z table can satisfy this query.

Now if we rewrite the above query as one of the below examples:

--Rewrite query to use constraint exclusion --
SELECT * FROM products
WHERE item_name BETWEEN 'A' AND 'AZ';

--Another example demonstrating combo 
-- and constraint exclusion works here too --
SELECT * FROM products
WHERE item_name < 'B' AND item_name LIKE 'A%';

We observe that our K-Z table falls out of our planner equation nicely.

Conclusion: Constraint Exclusion is nice, but it could be better

As shown above, constraint exclusion is fairly naive and when writing queries you have to take this into consideration. To use effectively when you have non-simple range conditions, you sometimes have to throw in a redundant condition to force it to kick in.

This makes it not easily usable in cases such as spatial partitioning of data with bounding boxes or more complex check conditions. In theory constraint exclusion can work nicely and more conveniently in many additional cases with some (hopefully minor) improvements of how it equates conditions. Hopefully we'll see some of these improvements in 8.4 or 8.5.