Re: citext like searches using index

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> On Apr 2, 2013, at 8:03 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Are there any widely known non-built-in cases besides citext?

>> Well, indxpath.c knows about text LIKE and network subset operators,
>> and it would be nice if it knew how to do the same type of optimization
>> for range inclusion, ie btree_indexed_col <@ range_constant. The latter
>> doesn't seem implementable in the current infrastructure because ranges
>> aren't all built-in types. I agree that the citext case isn't too
>> compelling in isolation, but surely the range case is interesting.

On further reflection, I withdraw the claim that range-inclusion
couldn't be implemented in the current design. Although the various
range types might not be built-in, the "anyelement <@ anyrange" operator
*is* built-in, so its OID could be added to the switch statements in
indxpath.c. I don't think it'd be terribly difficult to then add
datatype-agnostic code to pry apart the range value and construct a
derived "btree_indexed_col >= lowbound AND btree_indexed_col <= highbound"
indexclause. But this could not be extended to citext or other plugin
extensions, because their operators don't have hard-wired OIDs.

Anyway, even if the specific claim about ranges is bogus, I still think
there are enough data points to justify the idea that a more extensible
mechanism would be worth having. At the same time, there's a reason
why it's not yet got to the top of anyone's priority list.

> Is this knowledge encapsulated in a to-do?

I added an item to the "Indexes" section of the TODO page.

regards, tom lane