Re: SELECT with LIKE clause makes full table scan

Hi,

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:
> > > > > Why is this (ignoring the Index) and what could be done?
> > > [...]
> > > create INDEX d01ort on d01buch(d01ort) ;/* D01ORT */
> >
> > That index can't be used with a LIKE that has a trailing wildcard.
>
> Really? That seems to contradict the doc, i.e. default index type is B-Tree,
> which definitely supports trailing wildcard LIKE-predicates, as
> explicitly stated in said doc:
> https://www.postgresql.org/docs/14/indexes-types.html#INDEXES-TYPES-BTREE
>
> 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.
>
> PS: That also contradicts the small demo I made earlier up-thread:

relname datatype is name, which has a default C collation, so you are in the
only case that natively work for btree indexes:

# select unnest(indcollation)::regcollation from pg_index where indexrelid = 'pg_class_relname_nsp_index'::regclass;
unnest
--------
"C"
-
(2 rows)

I highly doubt that OP tables are also using C collation, so almost no one
does that.