Re: Can LIKE under utf8 use INDEXes? - Mailing list pgsql-general

On Wed, 2009-07-22 at 12:57 -0400, Robert James wrote:
> The docs suggest a workaround, to allow LIKE to use indexes - but I
> couldn't figure it out.  Although I'm stuck with locale utf8, all my
> data is 7-bit ascii.  I'm doing a tremendous amount of WHERE x LIKE
> 'abc%' - what's the best way to set up a good index?

Create the index using text_pattern_ops, and I think it will do what you
want.

CREATE INDEX foo_t_idx ON foo (t text_pattern_ops);

Regards,
    Jeff Davis