From: | Imre Samu <pella(dot)samu(at)gmail(dot)com> |
---|---|
To: | Matthias Apitz <guru(at)unixarea(dot)de> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: SELECT with LIKE clause makes full table scan |
Date: | 2022-01-26 14:20:02 |
Message-ID: | CAJnEWw=cRXuR1WGJ5WNb9YD8oDNBne3xWuhBjMMMmPnPWzDb2g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> 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 <guru(at)unixarea(dot)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, ✉ guru(at)unixarea(dot)de, http://www.unixarea.de/
> +49-176-38902045
> Public GnuPG key: http://www.unixarea.de/key.pub
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2022-01-26 15:03:48 | Re: SELECT with LIKE clause makes full table scan |
Previous Message | Julien Rouhaud | 2022-01-26 13:51:39 | Re: SELECT with LIKE clause makes full table scan |