Гость
Войти | Профиль | Очистить
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Как оптимизировать запрос с ILIKE? / 9 сообщений из 9, страница 1 из 1
03.03.2018, 22:54
    #39610116
Сварщик
Сварщик
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать запрос с ILIKE?
Здравствуйте!

Запрос стал бутылочным горлышком и здорово тормозит сайт, подскажите что-то можно сделать с запросом или базу надо переделывать?

PostgreSQL 10.2

Код: sql
1.
SELECT * FROM browscap WHERE 'Mozilla/5.0 (Linux; Android 6.0.1; SAMSUNG SM-T800 Build/MMB29K) AppleWebKit/537.36 (KHTML, like Gecko) SamsungBrowser/6.4 Chrome/56.0.2924.87 Safari/537.36'::text ILIKE useragent ORDER BY LENGTH(useragent) DESC;



Результат:
Запрос выполнен успешно. Общее время выполнения: 434 msec. Обработано строк: 5.
Код: plaintext
1.
2.
3.
4.
5.
6.
                          useragent character varying(255)                                                     data bytea
1 Mozilla/5.0 (%Linux%Android_6.0% Build/%) applewebkit% (%khtml%like%gecko%) SamsungBrowser/%Chrome%Safari%  [binary data]
2 Mozilla/5.0 (%Linux%Android% Build/%) applewebkit% (%khtml%like%gecko%) SamsungBrowser/%Chrome%Safari%      [binary data]
3 Mozilla/5.0 (%Linux%)%applewebkit%(%khtml%like%gecko%)%Chrome/% Safari/%                                    [binary data]
4 Mozilla/5.0 (%Linux%)%applewebkit%(%khtml%like%gecko%)%Chrome/%                                             [binary data]
5 %                                                                                                           [binary data]

[binary data] - сериализованный массив параметров.


План запроса:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
Sort  (cost=7613.88..7615.06 rows=472 width=552) (actual time=371.713..371.715 rows=5 loops=1)
  Sort Key: (length((useragent)::text)) DESC
  Sort Method: quicksort  Memory: 28kB
  ->  Seq Scan on browscap  (cost=0.00..7592.92 rows=472 width=552) (actual time=89.927..371.684 rows=5 loops=1)
        Filter: ('Mozilla/5.0 (Linux; Android 6.0.1; SAMSUNG SM-T800 Build/MMB29K) AppleWebKit/537.36 (KHTML, like Gecko) SamsungBrowser/6.4 Chrome/56.0.2924.87 Safari/537.36'::text ~~* (useragent)::text)
        Rows Removed by Filter: 94454
Planning time: 0.124 ms
Execution time: 371.753 ms
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.03.2018, 15:13
    #39610224
Ролг Хупин
Ролг Хупин
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать запрос с ILIKE?
Этого не достаточно?

Код: sql
1.
WHERE 'Mozilla/5.0'
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.03.2018, 16:00
    #39610230
Сварщик
Сварщик
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать запрос с ILIKE?
Ролг ХупинЭтого не достаточно?

Код: sql
1.
WHERE 'Mozilla/5.0'



Нет, это входящая строка, которой надо найти наилучшее соответствие.

Всё с ног на голову. Не строки в базе и шаблоном ищем, а шаблоны в базе и каждый из них применяется к строке.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.03.2018, 17:07
    #39610240
Ролг Хупин
Ролг Хупин
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать запрос с ILIKE?
СварщикРолг ХупинЭтого не достаточно?

Код: sql
1.
WHERE 'Mozilla/5.0'



Нет, это входящая строка, которой надо найти наилучшее соответствие.

Всё с ног на голову. Не строки в базе и шаблоном ищем, а шаблоны в базе и каждый из них применяется к строке.

Имеется в ввиду искать сначала левую часть, а затем в найденном полную строку
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.03.2018, 17:58
    #39610245
qwwq
qwwq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать запрос с ILIKE?
Сварщик,
можно нарезать шаблоны на массив значимых токенов, проиндексировать по этому массиву (гин-ом) и искать в обратную сторону -- кромсая строку поиска на массив , и речекая потом полной строкой.

как -то вот на эту тему:

 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
-- подбор способа кромсания на массивы
with dat as (SELECT 
useragent
,STRING_TO_ARRAY (regexp_replace(LOWER(useragent),'[\% \;\,\.\/\(\)]+','|','ig'),'|') arr --index 
FROM unnest(string_to_array(trim( $$
Mozilla/5.0 (%Linux%Android_6.0% Build/%) applewebkit% (%khtml%like%gecko%) SamsungBrowser/%Chrome%Safari%
Mozilla/5.0 (%Linux%Android% Build/%) applewebkit% (%khtml%like%gecko%) SamsungBrowser/%Chrome%Safari%
Mozilla/5.0 (%Linux%)%applewebkit%(%khtml%like%gecko%)%Chrome/% Safari/%
Mozilla/5.0 (%Linux%)%applewebkit%(%khtml%like%gecko%)%Chrome/%
$$
,e'\n')
,e'\n')) u (useragent)
)
select * from dat 
where 
	--indexed GIN 
	arr && string_to_array(regexp_replace(lower('Mozilla/5.0 (Linux; Android 6.0.1; SAMSUNG SM-T800 Build/MMB29K) AppleWebKit/537.36 (KHTML, like Gecko) SamsungBrowser/6.4 Chrome/56.0.2924.87 Safari/537.36') ,'[\% \;\,\.\/\(\)]+','|','ig'),'|')
-- recheck
AND 'Mozilla/5.0 (Linux; Android 6.0.1; SAMSUNG SM-T800 Build/MMB29K) AppleWebKit/537.36 (KHTML, like Gecko) SamsungBrowser/6.4 Chrome/56.0.2924.87 Safari/537.36'::text ILIKE useragent  --ORDER BY LENGTH(useragent) DESC

UNION ALL 

SELECT '',string_to_array(regexp_replace(lower('Mozilla/5.0 (Linux; Android 6.0.1; SAMSUNG SM-T800 Build/MMB29K) AppleWebKit/537.36 (KHTML, like Gecko) SamsungBrowser/6.4 Chrome/56.0.2924.87 Safari/537.36') ,'[\% \;\,\.\/\(\)]+','|','ig'),'|')


можно ещё подумать над триграммкой от ловера, но нужно ли -- надо проверять.


а исходная архитектура -- от очень большого ума.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.03.2018, 11:25
    #39610526
Alexius
Alexius
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать запрос с ILIKE?
Сварщик,

если достаточно ядер свободных на железке, можно parallel seq scan включить (см. max_parallel_workers_per_gather, max_parallel_workers). ресурсов будет потреблять примерно столько же, а выполняться быстрее.
по-хорошему надо как-то разбивать регекспы на непересекающиеся категории таким образом, чтобы не приходилось 90к регекспов выполнять, но может быть не сильно простая задача.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.03.2018, 12:40
    #39610579
Сварщик
Сварщик
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать запрос с ILIKE?
AlexiusСварщик,

если достаточно ядер свободных на железке, можно parallel seq scan включить (см. max_parallel_workers_per_gather, max_parallel_workers). ресурсов будет потреблять примерно столько же, а выполняться быстрее.
по-хорошему надо как-то разбивать регекспы на непересекающиеся категории таким образом, чтобы не приходилось 90к регекспов выполнять, но может быть не сильно простая задача.

Спасибо, как временное решение можно использовать. Всё-равно цифра большая, но уже легче копать варить.
Сейчас посмотрим как себя ещё боевой сервер поведёт.

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
                                                                                                     QUERY PLAN   $
------------------------------------------------------------------------------------------------------------------$
 Gather Merge  (cost=6911.00..6915.60 rows=394 width=552) (actual time=144.634..144.637 rows=5 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Sort  (cost=6910.97..6911.47 rows=197 width=552) (actual time=139.906..139.906 rows=2 loops=3)
         Sort Key: (length((useragent)::text)) DESC
         Sort Method: quicksort  Memory: 25kB
         ->  Parallel Seq Scan on browscap  (cost=0.00..6903.47 rows=197 width=552) (actual time=88.910..139.798 r$
               Filter: ('Mozilla/5.0 (Linux; Android 6.0.1; SAMSUNG SM-T800 Build/MMB29K) AppleWebKit/537.36 (KHTM$
               Rows Removed by Filter: 31485
 Planning time: 0.689 ms
 Execution time: 149.544 ms
(11 строк)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.03.2018, 12:43
    #39610580
Сварщик
Сварщик
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать запрос с ILIKE?
qwwqСварщик,
можно нарезать шаблоны на массив значимых токенов, проиндексировать по этому массиву (гин-ом) и искать в обратную сторону -- кромсая строку поиска на массив , и речекая потом полной строкой.

как -то вот на эту тему:

 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
-- подбор способа кромсания на массивы
with dat as (SELECT 
useragent
,STRING_TO_ARRAY (regexp_replace(LOWER(useragent),'[\% \;\,\.\/\(\)]+','|','ig'),'|') arr --index 
FROM unnest(string_to_array(trim( $$
Mozilla/5.0 (%Linux%Android_6.0% Build/%) applewebkit% (%khtml%like%gecko%) SamsungBrowser/%Chrome%Safari%
Mozilla/5.0 (%Linux%Android% Build/%) applewebkit% (%khtml%like%gecko%) SamsungBrowser/%Chrome%Safari%
Mozilla/5.0 (%Linux%)%applewebkit%(%khtml%like%gecko%)%Chrome/% Safari/%
Mozilla/5.0 (%Linux%)%applewebkit%(%khtml%like%gecko%)%Chrome/%
$$
,e'\n')
,e'\n')) u (useragent)
)
select * from dat 
where 
	--indexed GIN 
	arr && string_to_array(regexp_replace(lower('Mozilla/5.0 (Linux; Android 6.0.1; SAMSUNG SM-T800 Build/MMB29K) AppleWebKit/537.36 (KHTML, like Gecko) SamsungBrowser/6.4 Chrome/56.0.2924.87 Safari/537.36') ,'[\% \;\,\.\/\(\)]+','|','ig'),'|')
-- recheck
AND 'Mozilla/5.0 (Linux; Android 6.0.1; SAMSUNG SM-T800 Build/MMB29K) AppleWebKit/537.36 (KHTML, like Gecko) SamsungBrowser/6.4 Chrome/56.0.2924.87 Safari/537.36'::text ILIKE useragent  --ORDER BY LENGTH(useragent) DESC

UNION ALL 

SELECT '',string_to_array(regexp_replace(lower('Mozilla/5.0 (Linux; Android 6.0.1; SAMSUNG SM-T800 Build/MMB29K) AppleWebKit/537.36 (KHTML, like Gecko) SamsungBrowser/6.4 Chrome/56.0.2924.87 Safari/537.36') ,'[\% \;\,\.\/\(\)]+','|','ig'),'|')


можно ещё подумать над триграммкой от ловера, но нужно ли -- надо проверять.


а исходная архитектура -- от очень большого ума.

Спасибо, попробую.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.03.2018, 13:31
    #39610617
bdsm_sql
bdsm_sql
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать запрос с ILIKE?
авторнаилучшее соответствие
тогда может limit 1?

ещё предложение:

Код: plsql
1.
2.
3.
4.
5.
select t1.useragent, t2.data from (
  SELECT useragent FROM browscap
  WHERE 'Mozilla/5.0 <...>/537.36'::text ILIKE useragent
  ORDER BY LENGTH(useragent) DESC /*limit 1*/
) t1 join browscap t2 on t2.useragent = t1.useragent


(нужен индекс на useragent)

это если data нужна, а если нет, то вообще убрать:
Код: plsql
1.
2.
3.
4.
SELECT useragent
FROM browscap
WHERE 'Mozilla/5.0 <...>537.36'::text ILIKE useragent
ORDER BY LENGTH(useragent) DESC /*limit 1*/
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Как оптимизировать запрос с ILIKE? / 9 сообщений из 9, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 595ms
Закрыть
start [/forum/topic.php?fid=53&tablet=1&tid=1995906]:
 0ms
get settings:
 8ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 223ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 45ms
get tp. blocked users:
 2ms
others: 282ms
total:  595ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]