> We face in a PostgreSQL 11.4 installation on a potent Linux host a > ... > Why is this (ignoring the Index) and what could be done?
IMHO: 11.4 is very old. ( Release date: 2019-06-20 ) and missing a lot of patches. The latest patch release is 11.14 ( see https://www.postgresql.org/docs/11/release.html ) You can test the PG11.14 with the PostgreSQL docker image ( https://hub.docker.com/_/postgres ) - docker pull postgres:11.14-bullseye - import table ( d01buch ) - create indexes - test your query Regards, Imre Matthias Apitz <g...@unixarea.de> ezt írta (időpont: 2022. jan. 26., Sze, 11:55): > > 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: > > 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, ✉ g...@unixarea.de, http://www.unixarea.de/ > +49-176-38902045 > Public GnuPG key: http://www.unixarea.de/key.pub > > >
