Re: SELECT with LIKE clause makes full table scan

Julien Rouhaud <rjuju123@gmail.com> writes:
> On Wed, Jan 26, 2022 at 02:34:21PM +0100, Dominique Devienne wrote:
>> On Wed, Jan 26, 2022 at 1:13 PM Julien Rouhaud <rjuju123@gmail.com> wrote:
>>> That index can't be used with a LIKE that has a trailing wildcard.

>> So what makes you say that? --DD

> This part of the documentation you mentioned:

>> However, if your database does not use the C locale you will need to create
>> the index with a special operator class to support indexing of
>> pattern-matching queries; see Section 11.10 below.

Note that declaring the index with C collation should also work,
and might be preferable to using the pattern_ops opclass.
C collation has at least some chance of being used explicitly
in queries, whereas a pattern_ops index is basically never going
to match anything but LIKE/regex searches.

            regards, tom lane