Implied Functional index use (redux) - Mailing list pgsql-hackers

In a thread in July last year, I raised the possibility of transforming
a query to allow functional indexes to be utilised automatically.
http://archives.postgresql.org/pgsql-hackers/2006-07/msg00323.php

This idea can work and has many benefits, but there are some
complexities. I want to summarise those issues first, then make a more
practical and hopefully more acceptable proposal.

Taken together the complexities would have lead us to have additional
TRANSFORMABLE clauses on TYPEs, FUNCTIONs and potentially encoding
schemes. All of which, I agree, just too much complexity to allow this
to be specified.

One example of this was FLOAT, where -0 and +0 are equal but not the
same in a binary form. That would normally mean we couldn't use FLOAT
for TRANSFORMABLE indexes, but of course what happens if we specify a
partial functional index, where we only index values > 0. In that case,
we *can* use the transform technique again. Worse still we may have a
full (non-partial) index where there is a constraint on the column(s)
such as CHECK (value > 0). So we'd need another heavy dose of
catalog-complexity to catch all the special cases. 
Yuck and double Yuck.

Even if we did that, it isn't easy for a data type author to tell
whether their type is transformable, or not **in all cases**. That would
probably lead to people saying DISABLE TRANSFORM for their data type,
just in case. Which means no benefit in practice with this feature.

- - - 

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 ...];

ENABLE TRANSFORM is only possible for functional indexes.

Suggestions for better syntax/naming welcome. 

Placing the TRANSFORM clause on the index as a simple boolean makes
utilising the feature more streamlined at planning time too. This would
be an extra initial check in create_index_paths() to see if the query
might benefit from transform. Most indexable WHERE clauses would be able
to be transformed, if the index allows.

The feature would be enabled by default with a GUC, but as stated above,
the default for each index would be to *not* transform unless
specifically requested by the user.
enable_index_transform = on (default)| off

EXPLAIN would not need alteration, since the modified query would show
up clearly in the output. (I can add explicit visibility if people want
that).

Overall, a fairly isolated patch, with little user interface changes.

All of the complexities would be very clearly documented as part of this
feature. That is essential to avoid user error, of which I am mindful.
But the technique has much promise, so I would like to make this option
available to designers and DBAs.

If we can agree this smoothly, then it seems possible for 8.3. 

Comments?

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