Re: partitioned table query question

>
> 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.