Модераторы: LSD
  

Поиск:

Ответ в темуСоздание новой темы Создание опроса
> SQL запросы стали тормозить 
:(
    Опции темы
polin11
Дата 7.8.2018, 11:28 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Шустрый
*


Профиль
Группа: Участник
Сообщений: 122
Регистрация: 6.6.2015

Репутация: нет
Всего: нет



Использую СУБД Postgresql, относительно не так давно, SQL запросы стали выполняться намного дольше по времени. Некоторые запросы выполняются в несколько десятков раз дольше.
Провел анализ работы БД (сравнил с данными месячной давности), проблем с памятью или процами нет. Но заметил что время доступа к разделу с БД различается на порядок, что можно с эти сделать, 
кроме банального VACUUM FULL ANALYZE?
PM MAIL   Вверх
polin11
Дата 7.8.2018, 18:25 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Шустрый
*


Профиль
Группа: Участник
Сообщений: 122
Регистрация: 6.6.2015

Репутация: нет
Всего: нет



В запросе стал использоваться другой индекс, правильно ли я понимаю, после  
VACUUM FULL ANALYZE  должна обновиться статистика, которую использует планировщик для выбора оптимального
 способа выполнения запроса, также должен помочь REINDEX?
Есть ли еще способ кроме VACUUM FULL ANALYZE поставить на путь истинный планировщик?

PM MAIL   Вверх
Snowy
Дата 7.8.2018, 20:08 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Эксперт
****


Профиль
Группа: Модератор
Сообщений: 11363
Регистрация: 13.10.2004
Где: Питер

Репутация: нет
Всего: 484



Это естественный жизненный цикл - по мере накопления информации в таблице, может меняться план запроса.
Правильное решение - оптимизировать запрос, который стал работать плохо.
Таблица в будущем вряд ли будет становиться меньше.

Возможно запросу не хватает памяти для использования прежнего индекса.
Можно увеличить параметр work_mem в конфиг файле.

Можно удалить неэффективный индекс и построить новый, возможно составной.
PM MAIL   Вверх
polin11
Дата 8.8.2018, 20:05 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Шустрый
*


Профиль
Группа: Участник
Сообщений: 122
Регистрация: 6.6.2015

Репутация: нет
Всего: нет



К сожалению VACUUM и REINDEX не помогли. 
Как работает планировщик POSTGRES, для меня загадка. 
Есть такая же БД(тестовая, с аналогичным набором данных) на другом сервере, в аналогичных запросах, 
там используется нужный индекс, который раньше использовался на проблемной базе. 
Приведу элементарный запрос и планы выполнения

Код

SELECT Filed1
FROM Table1
WHERE Field2 like '01%' AND Field3 = 123


План запроса на проблемной БД:

Код

"Unique  (cost=0.43..6.21 rows=1 width=8) (actual time=1.323..11.502 rows=184 loops=1)"
"  Buffers: shared hit=4806 read=416"
"  ->  Index Scan using "Index1" on "Table1"  (cost=0.43..6.20 rows=1 width=8) (actual time=1.320..11.473 rows=184 loops=1)"
"        Index Cond: ("Filed3" = 123)"
"        Filter: ("Field2" ~~ '01%'::text)"
"        Rows Removed by Filter: 37833"
"        Buffers: shared hit=4806 read=416"
"Planning time: 26.205 ms"
"Execution time: 11.571 ms"


Используется Index1 по полю Field2 с классом оператора varchar_pattern_ops, запрос выполнялся 25 секунд


С аналогичной БД на другом сервере

Код

"Index Scan using "Index2" on "Table1"  (cost=0.69..8.71 rows=1 width=8) (actual time=0.052..0.426 rows=184 loops=1)"
"  Index Cond: (("Filed3" = 123) AND ("Field2" ~>=~ '01'::text) AND ("Field2" ~<~ '02'::text))"
"  Filter: ("Filed2" ~~ '01%'::text)"
"  Buffers: shared hit=189"
"Planning time: 0.558 ms"
"Execution time: 0.546 ms"


Используется Index2 по полям (Field2 с классом оператора varchar_pattern_ops, Field3), запрос выполнялся 50 миллисекунд 

Как-то оптимизировать запрос нельзя, куда уж проще. У меня осталась последняя мысль, удалить Index1,
но будет ли во всех местах, где действительно нужен Index1,  использоваться Index2 и не будет ли он тормозить эти запросы? 
Буду рад любым конструктивным идеям
PM MAIL   Вверх
Snowy
Дата 8.8.2018, 21:33 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Эксперт
****


Профиль
Группа: Модератор
Сообщений: 11363
Регистрация: 13.10.2004
Где: Питер

Репутация: нет
Всего: 484



Цитата(polin11 @  8.8.2018,  21:05 Найти цитируемый пост)
Как работает планировщик POSTGRES, для меня загадка. 
Неисповедимы пути планировщика.
В 9-й версии его улучшили. Рекомендую обновить до 10+, если это непринципиально.

Цитата(polin11 @  8.8.2018,  21:05 Найти цитируемый пост)
 У меня осталась последняя мысль, удалить Index1,
но будет ли во всех местах, где действительно нужен Index1,  использоваться Index2 и не будет ли он тормозить эти запросы? 

Поиск по полю 2 вполне может использовать Index2 и без второго условия. Так что, если где-то и упадёт производительность, то незначительно.
Так что достаточно смело можно удалять.

Ещё варианты:
Сделать копию базы и провести опыты:
Код
alter table Table1 alter column Field2 type text;
drop index Index1;
drop index Index2;
create index Index2 on Table1(Field2, Field3);


По примеру неочевидно, но, если нужен поиск именно по первым двум символам, то рекомендую убрать like и использовать полное равенство:
Код

create index Index3 on Table1(left(Field2, 2), Field3);
SELECT Field1 FROM Table1 WHERE left(Field2, 2)='01' AND Field3=123;


Попытаться заставить следовать прежним рекомендациям:
Код
SELECT Field1 FROM Table1 WHERE Field2 between '01' and '02' AND Field3 = 123;

Если копия базы ведёт себя неаналогично, то можно и на живой базе пробовать. Только отключить автокоммит или включить автороллбэк. И после опытов сделать вакуум.
PM MAIL   Вверх
polin11
Дата 11.8.2018, 22:52 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Шустрый
*


Профиль
Группа: Участник
Сообщений: 122
Регистрация: 6.6.2015

Репутация: нет
Всего: нет



Как можно скрыть оп планировщика поле Field2, чтобы использовался индекс по Fileld3. В запросе
Код

SELECT Filed1
FROM Table1
WHERE Field2 like '01%' AND Fileld3 = 123



В поле Field2 в нижнем регистре тип данных text
Вариант 1: 
Код

SELECT Filed1
FROM Table1
WHERE LOWER(Field2) like '01%' AND Fileld3 = 123



Вариант 2:
Код

SELECT Filed1
FROM Table1
WHERE  (Field2 like '01%')::integer=1 AND Fileld3 = 123


Может есть варианты более оптимальные?
PM MAIL   Вверх
polin11
Дата 13.8.2018, 19:59 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Шустрый
*


Профиль
Группа: Участник
Сообщений: 122
Регистрация: 6.6.2015

Репутация: нет
Всего: нет



помогли следующие волшебные команды

ALTER TABLE "Table1" ALTER COLUMN "Field2" SET STATISTICS 10000;
VACUUM ANALYZE "Table1";

Спасибо за помощь Snowy!!!!
PM MAIL   Вверх
  
Ответ в темуСоздание новой темы Создание опроса
1 Пользователей читают эту тему (1 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема | PostgreSQL | Следующая тема »


 




[ Время генерации скрипта: 0.1160 ]   [ Использовано запросов: 21 ]   [ GZIP включён ]


Реклама на сайте     Информационное спонсорство

 
По вопросам размещения рекламы пишите на vladimir(sobaka)vingrad.ru
Отказ от ответственности     Powered by Invision Power Board(R) 1.3 © 2003  IPS, Inc.