| From: | Julien Rouhaud <rjuju123(at)gmail(dot)com> |
|---|---|
| To: | Matthias Apitz <guru(at)unixarea(dot)de> |
| Cc: | Dominique Devienne <ddevienne(at)gmail(dot)com>, 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:21:12 |
| Message-ID: | CAOBaU_ZXuCZ1pigaoiBbq_23qcvWqEvLbyy1EmVQWYkVuMFJ8Q@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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? 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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Matthias Apitz | 2022-01-26 15:28:33 | Re: SELECT with LIKE clause makes full table scan |
| Previous Message | Matthias Apitz | 2022-01-26 15:07:28 | Re: SELECT with LIKE clause makes full table scan |