Sometimes, constraint exclusion fails to kick in, leading to very slow queries. There are limitations on constraint exclusion, which are as follows:
- The constraint exclusion setting can be disabled
- Constraint exclusion does not work if the where expression is not written in the equality or range manner
- Constraint exclusion does not work with non-immutable functions such as CURRENT_TIMESTAMP, so if the predicate in the WHERE clause contains a function that needs to be executed at runtime, then the constraint exclusion will not work
- A heavily partitioned table might decrease performance by increasing planning time
Let's assume that we want to partition a table based on a text pattern, such as pattern LIKE 'a%'. This can be achieved by rewriting the LIKE construct using range equality, such as pattern >='a ' and pattern < 'b'. So, instead of having a check constraint on a child table using the LIKE construct, one should have it based on ranges. Also, if a user performs a SELECT statement using the LIKE construct, the constraint exclusion will not work.