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.
ALTER DATABASE somedb SET constraint_exclusion=on;
ALTER TABLE product_items_j ADD CONSTRAINT chk_item_name CHECK (item_name LIKE 'J%');
The above constraint will never come into play in constraint exclusion. However if you wrote the constraint such as
ALTER TABLE product_items_j ADD CONSTRAINT chk_item_name CHECK (item_name BETWEEN 'J' AND 'JZ');
Then your constraint may be used in constraint exclusion scenario.
SELECT item_name FROM product_items WHERE item_name LIKE 'K%'
SELECT item_name FROM product_items WHERE item_name = 'Kettle'will be excluded by the above constraint.
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.
CREATE DATABASE playfield; ALTER DATABASE playfield SET constraint_exclusion=on; CREATE TABLE products ( 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) VALUES ('APPLE'),('APRICOT'), ('AVOCADO'), ('JACKFRUIT'), ('GUAVA'); INSERT INTO products_k_z(item_name) VALUES ('KOBE BEEF'),('WATERMELON'), ('LEMON');
---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.
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.