Re: SELECT with LIKE clause makes full table scan

On Wed, Jan 26, 2022 at 11:55 AM Matthias Apitz <guru(at)unixarea(dot)de> wrote:
> A SELECT with a LIKE clause on a table with ca. 5.200.000 rows on a
> column with an Index ignores this and does a full table scan:
>
> sisis=# explain (analyze, buffers) select * from d01buch where d01ort like 'Z 9610%' ;
> -> Parallel Seq Scan on d01buch (cost=0.00..679211.71 rows=128 width=952)...
> Filter: (d01ort ~~ 'Z 9610%'::text)

Could it be a collation issue? They need to match between the query
and the index typically.

See below how an index-scan transforms into a sequential-scan just
from using ILIKE instead of LIKE.
Just to illustrate how collations affect plans for prefix-like
queries. Show the relevant DDL for the `d01ort` column,
and its indexes, and that should help diagnose this.

Perhaps it could be related to statistics too? --DD

```
ddevienne=> explain select count(*) from pg_class where relname like 'PNS%';
-> Index Only Scan using pg_class_relname_nsp_index on pg_class
(cost=0.41..8.44 rows=5 width=0)
Index Cond: ((relname >= 'PNS'::text) AND (relname < 'PNT'::text))
Filter: (relname ~~ 'PNS%'::text)
Time: 1.647 ms

ddevienne=> explain select count(*) from pg_class where relname ilike 'PNS%';
Aggregate (cost=2682.35..2682.36 rows=1 width=8)
-> Seq Scan on pg_class (cost=0.00..2682.34 rows=5 width=0)
Filter: (relname ~~* 'PNS%'::text)
Time: 1.262 ms
```