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.
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:
WHERE item_name BETWEEN'A'AND'AZ';
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.
As far as I can see, the biggest problem with constraint exclusion is that a literal is required in the WHERE clause, effectively ruling out the use of a sub-select.
For example, the following will not use constraint exclusion:
SELECT * from my_partitioned_table WHERE name = (select name from my_other_table);