Re: SELECT with LIKE clause makes full table scan

El día miércoles, enero 26, 2022 a las 12:20:08 +0100, Josef Šimánek escribió:

> st 26. 1. 2022 v 11:55 odesílatel Matthias Apitz <guru(at)unixarea(dot)de> napsal:
> >
> >
> > Hello,
> >
> > We face in a PostgreSQL 11.4 installation on a potent Linux host a
> > serious performance degree.
> >
> > 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:
>
> Which index is ignored? Can you share the CREATE INDEX command as well?

/* # $Revision: 1.1.2.21 $ */
create UNIQUE INDEX d01sig on d01buch(d01gsi,d01ex) ;
/* alter table d01buch add constraint d01sig unique (d01gsi,d01ex)
deferrable initially deferred; *//* D01SIG */
create INDEX d01mcopyno on d01buch(d01mcopyno) ;/* D01MCOPYNO */
create INDEX d01bnr on d01buch(d01bnr) ;/* D01BNR */
create INDEX d01ort on d01buch(d01ort) ;/* D01ORT */
create INDEX d01mcopynozweig on d01buch(d01mcopyno,d01zweig) ;/* D01KATZWEIG */
create INDEX d01ort2 on d01buch(d01ort2) ;/* D02ORT2 */
create INDEX d01aufnahme on d01buch(d01aufnahme) ;/* D01aufnahme */
create INDEX d01titlecatkey on d01buch(d01titlecatkey) ;/* D01TITLECATKEY */
create INDEX d01invkrnr on d01buch(d01invkreis,d01invnr) ;/* D01invkrnr */

matthias

> > sisis=# explain (analyze, buffers) select * from d01buch where d01ort like 'Z 9610%' ;
> > QUERY PLAN
> > -------------------------------------------------------------------------------------------------------------------------------
> > Gather (cost=1000.00..680262.71 rows=510 width=952) (actual time=1324.096..1349.429 rows=1 loops=1)
> > Workers Planned: 4
> > Workers Launched: 4
> > Buffers: shared hit=102040 read=560674
> > -> Parallel Seq Scan on d01buch (cost=0.00..679211.71 rows=128 width=952) (actual time=1117.663..1315.062 rows=0 loops=5)
> > Filter: (d01ort ~~ 'Z 9610%'::text)
> > Rows Removed by Filter: 1055853
> > Buffers: shared hit=102040 read=560674
> > Planning Time: 2.028 ms
> > Execution Time: 1349.593 ms
> > (10 Zeilen)
> >
> > Why is this (ignoring the Index) and what could be done?
> >
> > Thanks
> >
> > matthias
> >
> >
> > --
> > Matthias Apitz, ✉ guru(at)unixarea(dot)de, http://www.unixarea.de/ +49-176-38902045
> > Public GnuPG key: http://www.unixarea.de/key.pub
> >
> >

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