On Dec 11, 2007, at 7:20 AM, Mike Rylander wrote:
On Dec 10, 2007 8:01 PM, Erik Jones <erik@xxxxxxxxxx> wrote:
[snip]
Again, though, is there some better way to go about implementing some
kind of hash based partitioning in postgres besides this that would
be more natural wrt queries?
Adding a column to hold the result of the %, perhaps updated by a
trigger so your app needn't change, and partitioning on that would be
the obvious way to get what you want today. If you have a byte or two
of slack space in the tuple (by alignment), just use a "char" or an
INT2. Assuming you don't affect fully aligned base tuple size, there
should be no table bloat, and no noticeable effect on speed. As far
as being more natural WRT queries, well, you'd add to your where
clause
bin = 34
instead of
some_id % 100 = 34
The former seems to me to be more natural from the narrow perspective
of the SELECT statement.
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.
Erik Jones
Software Developer | Emma®
erik@xxxxxxxxxx
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
