Re: partitioned table query question

On Dec 11, 2007, at 9:44 AM, Gregory Stark wrote:

>
> "Erik Jones" <erik(at)myemma(dot)com> writes:
>
>> 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.
>
> The problem Tom's tried to explain is that the function may or may not
> preserve the bin. So for example if you wanted to bin based on the
> final digit
> of a numeric number, so you had a constraint like
>
> CHECK substring(x::text, length(x::text)) = 0
>
> And then you performed a query with something like "WHERE x = 1.0".
> The
> constraint would appear to exclude all but bin 0. Whereas in fact it's
> possible that records with the value "1" would appear in bin 1.
>
> What's needed to make this work is some knowledge in the planner
> that the
> numeric->text cast does not preserve the equality property of the
> numeric
> operator class.
>
> This would be the same information that would be needed to
> expression indexes
> more useful. So if you had an expression index on "substring(name,
> 1,3)" and
> performed a query with a clause like "WHERE name = 'Gregory'" it could
> intelligently perform an index scan on the key "Greg" and then
> recheck the key
> "Gregory" against the table column.
>
> The problem is that that's quite a lot of machinery. It's not just
> a boolean
> flag for each function since there could be multiple "equals". Also
> you want
> to know separately whether it preserves equality and whether it
> preserves the
> entire btree ordering. So you potentially need a whole new table
> with every
> combination of btree operator class and function and several
> boolean columns
> for each combination.

Ok, that all makes sense and I can see that that's what Tom was
saying, it just took a bit of paraphrasing for me to get it.

>> 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.
>
> Or the near future.

sigh :)

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