Re: Index usage for tstzrange?

Heikki Linnakangas <hlinnakangas@vmware.com> writes:
> We should do this automatically. Or am I missing something?

Yes.  This is not equality.

> ALTER OPERATOR FAMILY integer_ops USING btree ADD
>   OPERATOR 3 <@ (int4, int4range),
>   FUNCTION 1 btint4rangecmp(int4, int4range);

That will break approximately everything in sight, starting with the
planner's opinion of what equality is.  There is *way* too much stuff
that knows the semantics of btree opclasses for us to start jamming
random operators into them, even if this seemed to work in trivial
testing.  (See the last section of src/backend/access/nbtree/README
to just scratch the surface of the assumptions this breaks.)

It's possible that for constant ranges we could have the planner expand
"intcol <@ 'x,y'::int4range" into "intcol between x and y", using
something similar to the index LIKE optimization (ie, the "special
operator" stuff in indxpath.c).  I'd like to find a way to make that
type of optimization pluggable, though --- the existing approach of
hard-wiring knowledge into indxpath.c has never been anything but
a kluge, and it definitely doesn't scale as-is to anything except
built-in types and operators.

            regards, tom lane