Re: [SQL] JOIN index/sequential select problem

gjerde(at)icebox(dot)org writes:
> Why in the world is postgres selecting seq scan on the inventorysuppliers
> table when doing an LIKE? That doesn't make sense to me.

I'm guessing you might be compiling with LOCALE support turned on?
The parser's hack to make LIKE comparisons indexable is only half
functional in that case, since you get the >= comparison but not the <=
one. Given the small size of your tables, the optimizer is probably
estimating that an index scan isn't going to be selective enough to
justify its extra cost.

FWIW, I do get an index scan plan on an attempt to duplicate this
case... but I'm not using LOCALE.

We need to figure out a way to make LIKE indexable in non-ASCII locales.
I think the best bet might be to try to generate a string "one greater"
than the given initial string. In other words, given
field LIKE 'ABC%'
we want to transform to
field LIKE 'ABC%' AND field >= 'ABC' AND field < 'ABD'
so that the optimizer can use the last two clauses to constrain the
index scan.

But it's not real obvious how to generate a "larger" string in the
general case with multibyte characters and non-straightforward collation
order. Anyone have an idea how to do that?

regards, tom lane