Re: spgist text_ops and LIKE

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Wed, Feb 1, 2012 at 10:57 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Too lazy to look at the code right now, but I think indxpath.c contains
>> hardwired assumptions that LIKE prefix optimizations are only possible
>> with btree indexes. Possibly it would be worth relaxing that.

> Is it as simple as the attached?

Yeah, that looks about right, with the single quibble that the other
places where we have #defines in pg_opfamily.h put them after the
corresponding DATA line not before. Please fix and commit.

> On the flip side, spg_text_inner_consistent()
> seems to fall back to a full index-scan for the regular comparison
> operators, which makes me wonder why anyone would bother having the
> operator at all.

MHO is that that code path is only useful when you are in C locale;
but I think that's a useful enough case to be worth the rather small
amount of incremental code to handle it. I'm not sure I believe your
speculations about making spgist handle inequality searches in other
locales. Non-C sort orders are too bizarre and we have too little
visibility into the precise rules the current locale is following.

... however ... could it be reasonable to apply strxfrm and store the
result of that in the index? Then searches would just need byte-by-byte
comparisons.

regards, tom lane