constraint_exclusion
adrianlzt opened this issue · 4 comments
I see that you recommend setting constraint_exclusion = on.
From the postgres doc:
Currently, constraint exclusion is enabled by default only for cases that are often used to implement table partitioning. Turning it on for all tables imposes extra planning overhead that is quite noticeable on simple queries, and most often will yield no benefit for simple queries. If you have no partitioned tables you might prefer to turn it off entirely.
By default it takes value partition.
So what's the point of turning on completely?
Thanks for the guide!
After reading more postgres docs, looks like the parameter that should be turned on is enable_partition_pruning (by default is on).
constraint_exclusion is for partition tables with inheritance
@adrianlzt thanks for bringing this up as I missed this point!
constraint_exclusion allows the query planner to avoid including partitions in a query when it can prove they can't provide useful rows to satisfy it.
When I originally wrote the docs it was geared around PostgreSQL version 10 and the official docs suggested to have constraint_exclusion enabled for declarative partitioning.
Since the release of PostgreSQL 11, the official version 11 docs do suggest to use the enable_partition_pruning for declarative partitioning.
The good news is both are enabled by default; but it should be known to the user to make sure they are enabled.
I'll update the notes to address the difference.
@adrianlzt, I updated the notes to address your input. Please let me know if you have any more questions.
All good. Thanks!