On Dec 11, 2007, at 9:44 AM, Gregory Stark wrote:
"Erik Jones" <erik@xxxxxxxxxx> 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@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 6: explain analyze is your friend
