Re: partitioned table query question

"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.

> 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!