Re: Implied Functional Index use

On Wed, 2006-07-12 at 22:34 -0400, Tom Lane wrote:

> More generally, I don't believe in hacks that only work for a small
> number of built-in types: to me, that's prima facie evidence that you
> haven't thought the problem through.

I agree an attempt at a simple definition of the required functional
properties hasn't yielded a great solution, so we must go deeper.

On Wed, 2006-07-12 at 15:09 +0200, Peter Eisentraut wrote:

> From a mathematical point of view, it seems cleaner to attach this property to
> functions, not operators, namely, "this function preserves the following
> relations". This would allow extending Simon's idea to other operators such
> as > and < and possibly more mind-boggling cases with geometric operators and
> such.

Well, in PG, operators are functions that define various special
properties of their inputs/outputs, so it seems the correct place to put
these definitions. I agree it seems more normalised to place this
information at the function itself, but that is not current precedent.

On Thu, 2006-07-13 at 09:14 +0200, Zeugswetter Andreas DCP SD wrote:

> Maybe we could have a tri (or more) state flag for the equality
> operators.
>
> ' ' .. not an equality op
> 'e' .. equality
> 's' .. strict equality (op only true iff the binary representation is
> equal)

Tom has pointed out that the required functional properties are actually
a fourth state between equality and full binary equality.

I was trying to avoid introducing new single-use properties, but I think
that is the only way here. My concern was not the complexity of
specifying this for function authors, but the problem of making an
incorrect selection leading to incorrect query results.

It seems we need this in the catalog:

> ' ' .. not an equality op
> 'e' .. equality (sufficient for FKs)
'f' .. functional equality (sufficient for this proposed optimisation)
> 's' .. strict equality (op only true iff the binary representation is
> equal)

We're breaking new ground here, but that's a good thing. I'd much rather
have an optimisable and extensible type system than a hard-wired one.

There is a problem of implication here, AFAICS:
When a user SQL asks
WHERE col1 = 7
which equality level is meant when several exist?

We currently already assume that they mean e-equality, since it is the
most useful and intuitive meaning. That is not as strict as f-equality,
so we would not be able to make implications from that.

I'll think on this some more, but not for 8.2.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com