Well, given that the bin is computed as a function of some_id, the
most natural way would be to not have to mention that bin in SELECT
statements at all. However, it does appear that either a.) including
the bin as a table attribute and in the where clause (either directly
or the computation) or b.) precomputing the bin and directly
accessing the child table will be the only options we have for now.
It occurs to me that if you are going to have to compute the bin anyway, you can also determine which table you need to work with directly.
And if you can do that you can modify the table name in the query instead of the adding an extra condition. This will save you a (short) step in the query plan, by avoiding checking the parent table for any matching rows. It may be a very small difference, but hey, it adds up.
The downside, that my application code needs to be aware of partitioning at the database layer, seems equivalent either way. And to be clear this is a big downside for me, I'm going to have to make some significant application layer changes to take advantage of partitioning, and if we later decide to change our partitioning rules in the future, we're going to have to update the application logic again. I'll willing to bite that bullet now, but just want to register my disappointment that partitioning isn't able to handle this common case more effectively. I hope it will handle it better in some future release.
In effect, all partitioning is doing for you in this case is giving you a more simple way to query the entire set of tables at once, rather than building a query that UNIONs all the tables. I also guess that if you do any bulk insert via COPY or INSERT ... SELECT, and have an insert trigger on the parent table, then that will help you route the inserted rows to the appropriate child tables. Of course the trigger is doing the work in that case as well, not the partitioning.
