There are several ways to solve the problem:
1. Convert to common numeric format for all numbers, ala Oracle
2. Promote for comparison during the index scan
3. Promote index boundary values for comparison in query planner only Convert back to index column type for actual
scan
Option 1 doesn't solve the general problem, has a space / performance penalty,
and would be a major change.
Option 2 involves making serious changes to every index access method, and
also has a performance penalty.
Option 3 appears to me to be the way to go. The main general requirement is
method similar to typeInheritsFrom() in backend/parser/parse_func.c to
determine whether a true promotion is possible for a pair of non-complex data
types.
One thing I am not clear on is how much re-planning is done when a query is
executed with different parameter values. If re-planning is not done, is it
acceptable to make minor plan changes according to the parameter values?
For example, it would be necessary to change a "<" operator to a "<=" operator
to get proper index scan behavior on a smallint index if the original right
hand side was greater than 32767.
- Mark
Thomas Lockhart wrote:
> That is why the index is not used: the backend is promoting all of the
> int2 column values to
> int4 for the comparison, and concludes that the available index is not
> relevant.
>
> The index traversal code would need to know how to promote individual
> values in the index for comparison, which is an interesting idea but I
> haven't thought about how efficient it would be. Clearly the cost would
> be different than a simple comparison.