| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | Julien Rouhaud <rjuju123(at)gmail(dot)com> |
| Cc: | Dominique Devienne <ddevienne(at)gmail(dot)com>, Matthias Apitz <guru(at)unixarea(dot)de>, Josef Šimánek <josef(dot)simanek(at)gmail(dot)com>, pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
| Subject: | Re: SELECT with LIKE clause makes full table scan |
| Date: | 2022-01-26 15:03:48 |
| Message-ID: | 2438893.1643209428@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
Julien Rouhaud <rjuju123(at)gmail(dot)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(at)gmail(dot)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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Matthias Apitz | 2022-01-26 15:07:28 | Re: SELECT with LIKE clause makes full table scan |
| Previous Message | Imre Samu | 2022-01-26 14:20:02 | Re: SELECT with LIKE clause makes full table scan |