"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. > 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. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
