Как уменьшить время выполнения запроса like '%uri%' в Postgresql?

Question

[Entity34]

В базе 40,984,424 записей

делаю запрос
select * from "db1" ps where "Url" like '%profilesettings%'


Как уменьшить время до 1 секунды на запрос ?

explain analyse select * from "db1" ps where "Url" like '%profilesettings%'

Gather  (cost=1000.00..2359455.91 rows=3141 width=439) (actual time=0.590..4487.742 rows=220 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on "db1" ps  (cost=0.00..2358141.81 rows=1309 width=439) (actual time=13.173..4311.107 rows=73 loops=3)
        Filter: ("Url" ~~ '%profilesettings%'::text)
        Rows Removed by Filter: 13994795
Planning Time: 0.214 ms
Execution Time: 4487.844 ms

Comments

[Модератор]

Не надо ставить как можно больше тэгов. Лучше оставить один, но конкретный, с которым проблема.
См.п.3.1 Регламента.

[Entity34]

Модератор, ok)

[Slava Rozhnev]

Взять сервер помощнее

[DollyPapper]

Slava Rozhnev, и как с данным планом выполнения запроса это должно помочь?

[rPman]

А задача то таким запросом выполняется корректно? искомое слово может встречаться как в доменном имени, так и в пути и в параметрах http GET запроса, включая информацию из #anchor

Как минимум, если предполагается поиск по составным частям, я бы при записи url в базу разбирал бы ее по частям, строил бы по полученному хранилищу отдельные индексы (и получил бы высокую нагрузку на запись, но все решаемо)

Answer 1

[Дмитрий]

1. GIN индексы - https://medium.com/@maanavshah/performance-optimiz...

2. Через костыли: сделать составной индекс на url и на reverse(url) и искать как url like 'setting%' and reverse(url) like reverse('%setting')

Answer 2

[Melkij]

create extension pg_trgm;
create index concurrently db_url_trgm_idx on db1 using gin ("Url" gin_trgm_ops);

https://www.postgresql.org/docs/current/pgtrgm.html

Answer 3

[Entity34]

GIN индексы это скорость )
теперь запросы за 200ms делаются

select * from "db1" ps where "Url" like '%profilesettings%'

Bitmap Heap Scan on "db1 " ps  (cost=740.34..12974.90 rows=3141 width=439) (actual time=139.469..139.912 rows=220 loops=1)
  Recheck Cond: ("Url" ~~ '%profilesettings%'::text)
  Rows Removed by Index Recheck: 19
  Heap Blocks: exact=238
  ->  Bitmap Index Scan on db_url_trgm_idx  (cost=0.00..739.56 rows=3141 width=0) (actual time=139.435..139.436 rows=239 loops=1)
        Index Cond: ("Url" ~~ '%profilesettings%'::text)
Planning Time: 0.418 ms
Execution Time: 139.946 ms