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@xxxxxxxxx> 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.
