Hello ,I have a parent table and 6 child tables (partitions). The child tables have check constraints defined in the formCHECK (myuid in (123,456,..)).myuid is bigint, the constraints for the 6 child tables are definitely mutually exclusive. The number of values in the list ranges from 2-10 for 5 of the child tables. For the 6th child table, the list is 2500+ elements. When I try explain/explain analyze for even a simple query likeselect * from parent where myuid in (123,456,789)the child table with 2500+ elements gets always scanned. I have an index on the column and that does get used. But why doesn't the planner just use constraint exclusion and not go for the index scan? Anyone faced a similar issue?
IIRC The planner doesn't understand
overlaps so having a definition of:
IN (1,2,3,4,5); or nearly equivalently = ANY(ARRAY[1,2,3,4,5]))
and a request for:
IN (1,3,5) / = ANY(ARRAY[1,3,5]) is going to get you nowhere with the planner.
I am not sure but am doubting it is intelligent enough to recognize the functional _expression_ even if all of the values are present. "simple equality" (http://www.postgresql.org/docs/9.4/interactive/ddl-partitioning.html) this is not.
David J.
