Next, in postgresql.conf, what is the value of constraint_exclusion ?
partition
In response to the other possible issues pointed out - the planner is indeed skipping the rest of the child tables (the ones with just a few values in the check constraint). That is why I feel the number of elements in the check constraint on this particular child table is causing it to be scanned. The query ends up scanning the table where the data will be found and the table with 2500+ values in the check constraint. I may be missing something?
I tried changing the filter from myuid in (1,2,3) to myuid = 1 or myuid = 2 or....
It did not improve the plan. One Index Cond became 3 Index Cond .