From: | Matthias Apitz <guru(at)unixarea(dot)de> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | SELECT with LIKE clause makes full table scan |
Date: | 2022-01-26 10:55:38 |
Message-ID: | YfEoqiHQikgT82PB@c720-r368166 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 | Josef Šimánek | 2022-01-26 11:20:08 | Re: SELECT with LIKE clause makes full table scan |
Previous Message | Ivan E. Panchenko | 2022-01-26 10:38:01 | Re: Counting the number of repeated phrases in a column |