Learning PostgreSQL 11 - Third Edition

Constraint exclusion limitations

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 doesn't work if the where expression isn't written in the equality or range manner

• Constraint exclusion doesn't 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, the constraint exclusion won't 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 ...