Re: SELECT with LIKE clause makes full table scan

El día miércoles, enero 26, 2022 a las 11:21:12p. m. +0800, Julien Rouhaud escribió:

> Hi,
>
> On Wed, Jan 26, 2022 at 11:07 PM Matthias Apitz <guru(at)unixarea(dot)de> wrote:
> >
> > We changed two relevant Indexes to
> >
> > CREATE INDEX d01ort ON d01buch(d01ort bpchar_pattern_ops );
> > CREATE INDEX d01ort2 ON d01buch(d01ort2 bpchar_pattern_ops );
>
> When you said changed, did you drop the previous ones?

Yes, of course.

> As Tom
> mentioned, those indexes are specialized and are only useful for LIKE
> 'something%' queries. It's quite likely that your existing indexes
> were useful for other queries, which may not be as fast without those
> indexes. You can check in pg_stat_user_indexes if your indexes seems
> to be used before actually dropping them for instance:
> https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-INDEXES-VIEW

Thanks, we're still investigating more cases with LIKE clause and will
consider your hint.

matthias

--
Matthias Apitz, ✉ guru(at)unixarea(dot)de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub