But how constraint exclusion would react with the following queries …
b- Select * from parent_table where id between 2345 and 6789; -- using a range of ids
Not sure...
These are constants but I'm not sure how smart the planner is about figuring out inequalities of this form.
I would guess yes but it should be easy enough to confirm this yourself using explain...you already have the tables.
c- Select * from parent_table where id in(select ids from anothertable); -- using a list of ids from a select
Definitely no...
Constraint exclusion is done by the planner before data is read so there is no possible way for data in a table to be used.
As for performance the only way to know for sure is to test your usage patterns and data. Even without constraint exclusion partitioning can provide benefits.
David J.
