Re: Text pattern JOINs that use indexes

Richard Brooksby <rb@ravenbrook.com> writes:
> Well, you can write that, but it won't use a btree index on
> bar_strings(string) because the planned doesn't know that the prefix
> doesn't contain wildcards.  So instead we have to plan each lookup with
> a constant string:

Another possibility is to manually hack up the query with the index
boundary conditions that the planner won't generate because it's not
sure about the wildcard situation.  Something like

    select * from prefixes, strings where
        string like (prefix || '%')
        and string >= prefix
        and string <= (prefix || '~~~~~~~');

The trick here is to generate the upper bound string correctly ---
I've cheated quite a lot in the above example by assuming that '~'
sorts larger than any character you'd actually have in your strings.
But if you know your data well you may be able to do this reliably.

Beware that the above will almost certainly not work if you're not
running in C locale.  Other locales have bizarre sorting rules that
will cause the >=/<= range to not match the prefix very well.  However,
if you are getting indexscan plans with plain constant patterns then
you are in C locale, because the planner can't solve that problem either...

            regards, tom lane