Re: partitioned table query question

On Mon, 2007-12-10 at 23:18 -0500, Tom Lane wrote:
> Erik Jones <erik(at)myemma(dot)com> writes:

> If you dig in the PG archives you will find some discussions of
> inventing a "real equality" flag for operators, which would authorize
> the planner to make such deductions for any immutable operator/function.
> The idea hasn't gone anywhere, partly because it's not clear that it
> would really help in very many common cases.

I think the only thing we can really say is that we haven't done it yet,
everything takes time and this is a tricky problem.

People are asking for this to work with an integer to allow hashing. We
already use that exact concept internally for hash aggregation and hash
joins, so I don't think we can argue it wouldn't help in common cases.

> The fact that we could
> *not* set the flag on such common cases as float and numeric equality
> is a bit discouraging in that connection.

With respect, that argument is circular. You required the implementation
to be a marking of the operator in that way, which leads to the
restriction you note. It can work for float and numeric, just not in all
conceivable cases. Most datatypes have some kind of weirdness that
prevents us from specifying it for everything in an easy manner.

Can I ask you to please lead us out of this impasse? The main problem is
working out an acceptable way to mark operators/datatypes/whatever or
combinations or thereof as "able to be transformed". Once we have that,
working out the rest is complex but doable. And it will then allow
partitioning, partial indexes and functional indexes to have a wider
range of application.

We really only want this for
- hashing functions, various, mostly on integers, uuids, text?
- upper() and lower() on text
- substr() on text
- very few others

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com