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
- Its disabled by default. You need to either change your postgresql.conf to constraint_exclusion = on. You can also enable it on a session by session basis.
Alternatively if you don't want it enabled for all your databases but don't want to always have to toggle it on, you can enable it per database with a command
ALTER DATABASE somedb SET constraint_exclusion=on; - Constraint_Exclusion only works for range or equality check constraints.
This means that if you add a check constraint such as
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. - Even if you have a range check constraint and your where condition should rightly be constrained, it often is not
if your WHERE condition is not written in a range like manner. For example
SELECT item_name FROM product_items WHERE item_name LIKE 'K%'
will not be excluded by the above check constraint, however
SELECT item_name FROM product_items WHERE item_name = 'Kettle'will be excluded by the above constraint.
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.
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.
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.