Re: partitioned table query question

On Dec 11, 2007, at 7:20 AM, Mike Rylander wrote:

> On Dec 10, 2007 8:01 PM, Erik Jones <erik(at)myemma(dot)com> 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(at)myemma(dot)com
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