Re: Implied Functional index use (redux)

On Thu, 2007-01-25 at 16:20 -0500, Tom Lane wrote:
> "Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> > A simpler, alternate proposal is to allow the user to specify whether a
> > functional index is transformable or not using CREATE or ALTER INDEX,
> > with a default of not transformable. That then leaves the responsibility
> > for specifying this with the user, who as we have seen is the really
> > only person really capable of judging the whole case on its merits.
>
> > e.g. CREATE INDEX fooidx ON foo (foofunc(foocol1))
> > [TABLESPACE ...] [ENABLE|DISABLE TRANSFORM] [WHERE ...];
>
> This is a foot-gun and nothing else. I hardly think the average DBA
> will realize such subtleties as "numeric equality doesn't guarantee that
> such-and-such works". If it's not specified by the datatype author
> it's not going to be safe.

OK, no problem.

The most beneficial use case is for string handling: name lookups, case
insensitive indexing and index size reduction generally. If, for some
reason, bpchar were to be excluded then it would take away a great chunk
of benefit.

Two questions:

- Will bpchar be transformable?

- Do you see a clear way forward for specifying the information required
to allow the transform? We need to specify the operator, which might be
taken to include the datatype. (Peter suggested placing this on the
function itself, though I think current precedent is to place on the
operator.) If you can say where you want the info to live, I can work
out the details and repost.

If there's clear benefit and a clear way forward, then we might just be
OK for 8.3. If not, I'll put this back on the shelf again in favour of
other ideas.

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