Запросам вредит обновление статистики индексов

Запросы, планы, оптимизация запросов, ...

Модераторы: kdv, CyberMax

BPach
Сообщения: 21
Зарегистрирован: 21 фев 2007, 12:59

Запросам вредит обновление статистики индексов

Сообщение BPach » 23 фев 2007, 11:23

Сервер FB 1.5.3 на Win XP. База в разработке (тестовом режиме).

Запрос:

Код: Выделить всё

SELECT a57.IDDEF as IDDEF, a57.IDCARD as ID, a57.VAL as F57, a88.VAL as F88, a112.VAL as F112, a113.VAL as F113
FROM VAL_STR a57, VAL_BOOL a88, VAL_STR a112, VAL_STR a113
WHERE a57.IDDEF=20 and a57.idfield=57 and a88.IDDEF=20 and a88.idfield=88 AND
      a88.IDCARD=a57.IDCARD AND a88.VAL=1 and
      a112.IDDEF=20 and a112.idfield=112 AND a112.IDCARD=a57.IDCARD and
      a113.IDDEF=20 and a113.idfield=113 AND a113.IDCARD=a57.IDCARD and
ORDER BY a57.VAL
До обновления статистики индексов выборка выполнялась с планом:

Код: Выделить всё

PLAN SORT (JOIN (a88 INDEX (VAL_BOOL_IDX5,VAL_BOOL_IDX1),a113 INDEX (VAL_STR_IDX5),a112 INDEX (VAL_STR_IDX5),a57 INDEX (VAL_STR_IDX5)))
за время:
Prepare time = 0ms
Execute time = 16ms
Avg fetch time = 16.00 ms

после обновления статистки с планом:

Код: Выделить всё

PLAN JOIN (a57 INDEX VAL_STR_IDX1),a113 INDEX (VAL_STR_IDX5),a112 INDEX (VAL_STR_IDX5),a88 INDEX (VAL_BOOL_IDX5,VAL_BOOL_IDX1))
и за время:
Prepare time = 0ms
Execute time = 32s 922ms
Avg fetch time = 32s 922.00 ms

Время выборки увеличилось более чем в 2 тысячи раз!!!

В таблице VAL_BOOL 52 072 записей, в таблице VAL_STR 408 262.
IBExpert показывает, что в первом случае индексированных чтений было 4, во втором >52000 (не индексированных чтений нет). Предполагаю, отсюда и время выполнения.
Результат выборки - одна запись.
Никаких других действий с базой (в целях чистоты эксперимента) не проводились.

Попытка обсуждения похожей проблемы была предпринята на этом форуме в августе прошлого года (см. тему "Firebird - изменился план запроса после backup/restore"), но кроме фразы Дмитрия Кузьменко "бывает такое. кардинальность таблиц после b/r изменилась. некая таблица стала "плотнее", т.е. меньше фрагментирована, наверняка.",
которая никак проблему не разъясняет (во всяком случае для меня), по существу вопроса ничего сказано не было (тема быстро перешла на обсуждение самого запроса, тем и закончилась).

Но вопрос остался: "что делать?"

Я не могу вручную подставлять нужный план из-за того, что приложение генерит великое множество вариантов запросов (похожих на представленный) и угадать (знать) порядок следования индексов в очередном варианте запроса, чтобы он работал быстро просто невозможно.

Перестройка запроса (допустим, в одном из экспериментов в запросе применялись явные join) ни к каким изменениям не приводит, результат один и тот же (формулируя проблему в этой базе): до обновления статистики индексов выборка выполняется быстро, после обновления - тормоза.

А решается ли в моем случае эта проблема вообще? Что-то не получается.

Подобные запросы успешно работают с 2003 года в различных базах и только в этой обнаружилась проблема.

IBAnalist показывает, что в базе много "плохих" индексов. Но после обновления статистики индексов как они были "плохими" так и остаются. И как в насмешку рекомендует (kdv, никоим образом не хочу задеть очень даже достойную программу, просто констатация факта):
"Обнаружено, что в базе данных есть индексы (32), у которых устарела информация о статистике. Это может привести к формированию оптимизатором некорректных планов запросов. Рекомендуется периодически выполнять команду set statistics index <index_name> для каждого не уникального индекса...".
Но именно после обновления статистики и происходит появление неоптимизированного плана. Впрочем, "не оптимизированный" и "не корректный", вполне возможно, разные вещи.

hvlad
Разработчик Firebird
Сообщения: 1244
Зарегистрирован: 21 мар 2005, 10:48

Re: Запросам вредит обновление статистики индексов

Сообщение hvlad » 23 фев 2007, 11:49

BPach писал(а):Результат выборки - одна запись
Для чего тогда ORDER BY ?
BPach писал(а):Но вопрос остался: "что делать?"

Код: Выделить всё

ORDER BY a57.VAL + 0

BPach
Сообщения: 21
Зарегистрирован: 21 фев 2007, 12:59

Re: Запросам вредит обновление статистики индексов

Сообщение BPach » 23 фев 2007, 13:20

Запрос универсальный и только в конкретной ситуации выдает одну запись. На самом деле может выдавать сколько угодно, поэтому ORDER BY.

При применение "ORDER BY a57.VAL + 0" выдается ошибка:
Unsuccessful execution caused by a system error that precludes
successful execution of subsequent statements.
Dynamic SQL Error.
expression evaluation not supported.
Скрипт запускаю в IBExpert.

hvlad
Разработчик Firebird
Сообщения: 1244
Зарегистрирован: 21 мар 2005, 10:48

Re: Запросам вредит обновление статистики индексов

Сообщение hvlad » 23 фев 2007, 13:35

BPach писал(а):При применение "ORDER BY a57.VAL + 0" выдается ошибка:
Unsuccessful execution caused by a system error that precludes
successful execution of subsequent statements.
Dynamic SQL Error.
expression evaluation not supported.
Скрипт запускаю в IBExpert.
Я должен знать какого типа a57.VAL ?

Пробуйте a57.VAL || ''

BPach
Сообщения: 21
Зарегистрирован: 21 фев 2007, 12:59

Сообщение BPach » 23 фев 2007, 13:41

a57.VAL поле типа varchar(50)

А подстановка "||" тоже дает ошибку, но парсера - "Unexpected end of command."

hvlad
Разработчик Firebird
Сообщения: 1244
Зарегистрирован: 21 мар 2005, 10:48

Сообщение hvlad » 23 фев 2007, 14:40

И это даёт ошибку ?

Код: Выделить всё

SELECT * FROM RDB$DATABASE
ORDER BY RDB$CHARACTER_SET_NAME || ''

BPach
Сообщения: 21
Зарегистрирован: 21 фев 2007, 12:59

Сообщение BPach » 23 фев 2007, 15:13

hvlad писал(а):И это даёт ошибку ?

Код: Выделить всё

SELECT * FROM RDB$DATABASE
ORDER BY RDB$CHARACTER_SET_NAME || ''
Это ошибку не дает. Но подстановка ORDER BY a57.VAL || '' ни к чему хорошему не приводит: все тот же тормоз. Ищем не там, видимо.

hvlad
Разработчик Firebird
Сообщения: 1244
Зарегистрирован: 21 мар 2005, 10:48

Сообщение hvlad » 23 фев 2007, 15:29

Смотри на отличия в планах. а57 сменил индекс

kdv
Forum Admin
Сообщения: 6595
Зарегистрирован: 25 окт 2004, 18:07

Сообщение kdv » 23 фев 2007, 15:46

Но именно после обновления статистики и происходит появление неоптимизированного плана. Впрочем, "не оптимизированный" и "не корректный", вполне возможно, разные вещи.
см. хелп по ibanalyst, "Дополнительные вопросы и ответы", последний пункт - "10. Как улучшить производительность при частых обновлениях данных"

kdv
Forum Admin
Сообщения: 6595
Зарегистрирован: 25 окт 2004, 18:07

Сообщение kdv » 23 фев 2007, 15:48

для совместимости обычно пишут:

Код: Выделить всё

select stringField||'' from table
order by 1
а не order by stringField||''

собственно, по поводу плана. возьмите например индекс с самой фиговой селективностью, и посмотрите, что именно по нему выбирается и хотя бы примерно сколько. и к этому условию припишите field+0 = ...
лишний индекс отрубится.

BPach
Сообщения: 21
Зарегистрирован: 21 фев 2007, 12:59

Сообщение BPach » 23 фев 2007, 16:29

hvlad писал(а):Смотри на отличия в планах. а57 сменил индекс
Да, сменил, а что из этого следует? Чего делать-то чтобы не менял? Беда в том, что все происходит помимо моей воли.

По совету kdv приписал к запросу "field+0=1" - стало гораздо лучше (6 сек). Кстати, не очень понятно в чем фишка подстановки пустого значения в условие, но работает. И тут, видимо, действительно надо выбирать индекс, а это исключено, потому как запросы формируются приложением в разных вариантах и смотреть какой индекс "с фиговой селлективностью" именно на этот запрос, не представляется возможным.

Собственно, речь о том как бы избежать изменений в худшую сторону при обновлении статистики индексов. Судя по всему, вопрос не решаемый.

hvlad
Разработчик Firebird
Сообщения: 1244
Зарегистрирован: 21 мар 2005, 10:48

Сообщение hvlad » 23 фев 2007, 16:40

BPach писал(а):Собственно, речь о том как бы избежать изменений в худшую сторону при обновлении статистики индексов. Судя по всему, вопрос не решаемый.
Правильной (не избыточной) схемой индексирования

kdv
Forum Admin
Сообщения: 6595
Зарегистрирован: 25 окт 2004, 18:07

Сообщение kdv » 23 фев 2007, 16:53

Кстати, не очень понятно в чем фишка подстановки пустого значения в условие, но работает.
планами крутишь, а не понимаешь. :) +0 - это ВЫРАЖЕНИЕ. А сервер не может использовать индекс для поиска по выражению. Потому что понятия не имеет, как выражение меняет исходное значение столбца.
Собственно, речь о том как бы избежать изменений в худшую сторону при обновлении статистики индексов. Судя по всему, вопрос не решаемый.
структурка у вас "объектная", как я понял. а значит перекосы по производительности будут и дальше. И вообще, чем больше будет данных, тем будет хуже. Такова судьба подобных моделей.
Пока не знаю, приговор это, или нет :)

Как минимум, мне кажется что в данном случае проблема - объединение одной и той же таблицы 3 раза. Возможно, стоит вообще прибить индекс VAL_BOOL_IDX5 или VAL_BOOL_IDX1. Или VAL_STR_IDX5. В данный момент я не знаю, по каким столбцам эти индексы построены. Может, надо добавить еще индексов по VAL_STR, а может убрать индекс VAL_STR_IDX1.

BPach
Сообщения: 21
Зарегистрирован: 21 фев 2007, 12:59

Сообщение BPach » 23 фев 2007, 16:59

hvlad писал(а):Правильной (не избыточной) схемой индексирования
Во как. А это уже интересно. Нет, правда. Можно (может быть и нужно) пересмотреть политику индексирования.
Вот только существующие индексы в каких-то запросах могут оказаться избыточны, а в каких-то самый раз.
Спасибо, hvlad за хоть тусклый, но свет в конце тунеля.

kdv
Forum Admin
Сообщения: 6595
Зарегистрирован: 25 окт 2004, 18:07

Сообщение kdv » 23 фев 2007, 16:59

и еще у меня есть смутные подозрения - скажите-ка, почему все индексы, упоминаемые в планах, созданы "вручную"? Есть-ли вообще первичные ключи, FK, и так далее, в этой структуре?

BPach
Сообщения: 21
Зарегистрирован: 21 фев 2007, 12:59

Сообщение BPach » 23 фев 2007, 17:18

kdv писал(а): планами крутишь, а не понимаешь. :) +0 - это ВЫРАЖЕНИЕ. А сервер не может использовать индекс для поиска по выражению. Потому что понятия не имеет, как выражение меняет исходное значение столбца.
Да, что-то я не догнал - заработался (и на старуху бывает проруха).
kdv писал(а):структурка у вас "объектная", как я понял. а значит перекосы по производительности будут и дальше. И вообще, чем больше будет данных, тем будет хуже. Такова судьба подобных моделей.
Пока не знаю, приговор это, или нет :)
Вы правы, kdv, производительность при большом кол-ве данных так себе. Правда, после некоторой "критической отметки" объема, не шибко от них зависит.
kdv писал(а):Как минимум, мне кажется что в данном случае проблема - объединение одной и той же таблицы 3 раза. Возможно, стоит вообще прибить индекс VAL_BOOL_IDX5 или VAL_BOOL_IDX1. Или VAL_STR_IDX5. В данный момент я не знаю, по каким столбцам эти индексы построены. Может, надо добавить еще индексов по VAL_STR, а может убрать индекс VAL_STR_IDX1.
Советы ваши приму во внимание со всей тщательностью. Низкий поклон и благодарность всем за то что не оставили на перекрестке судьбы (и может быть клиент не порежет меня на ремни).

BPach
Сообщения: 21
Зарегистрирован: 21 фев 2007, 12:59

Сообщение BPach » 23 фев 2007, 17:25

kdv писал(а):и еще у меня есть смутные подозрения - скажите-ка, почему все индексы, упоминаемые в планах, созданы "вручную"? Есть-ли вообще первичные ключи, FK, и так далее, в этой структуре?
Почему "вручную"? Да и как это: "создать индексы вручную"? А FK и PK существуют как и положено.

И как насчет подозрений?

kdv
Forum Admin
Сообщения: 6595
Зарегистрирован: 25 окт 2004, 18:07

Сообщение kdv » 23 фев 2007, 19:12

Советы ваши приму во внимание со всей тщательностью. Низкий поклон и благодарность всем за то что не оставили на перекрестке судьбы (и может быть клиент не порежет меня на ремни).
попробуйте взять статистику БД из IBAnalyst 2 (если не найдете, пришлю триал), и прислать ее в zip на support@ibase.ru.
Там будут видны метаданные и селективность.
Почему "вручную"? Да и как это: "создать индексы вручную"? А FK и PK существуют как и положено.

И как насчет подозрений?
"вручную" - это create index blabla...
ПК и ФК может и существуют, но я почему то этих индексов в плане не вижу. Может запрос такой, а может и ... :)

BPach
Сообщения: 21
Зарегистрирован: 21 фев 2007, 12:59

Сообщение BPach » 23 фев 2007, 21:25

IBAnalist 2 нет. Есть 1.9.5.1. Пришлите двойку, если не сложно.

kdv
Forum Admin
Сообщения: 6595
Зарегистрирован: 25 окт 2004, 18:07

Сообщение kdv » 24 фев 2007, 17:45

спасибо за статистику, заодно вижу подобие бажка при сравнении float.

Так вот - индекс VAL_STR_IDX1, который имея неплохую селективность (круче только индекс по ПК и тот самый VAL_STR_IDX5), на самом деле является действительно проблемным.
см. статистику -
ключей всего 408 тысяч. В этом индексе один из ключей повторяется 312 тысяч раз. !. То есть, 75% всех ключей - это ОДИН ключ. В остальных 100к ключей находятся 33к уникальных, т.е. примерно по 3 ключа имеют одинаковые значения.

Что это значит. Это значит, что такой индекс полезен только при поиске на равенство или в диапазоне тех самых "наиболее уникальных" ключей. И когда в = или диапазон попадает то самое значение, которое занимает 75% индекса - привет производительности.
Так что, если поиск по этому столбцу делается совсем не такой, как я описал - лучше этот индекс убить.

Однако, этот индекс построен по столбцу VAL, а я в приведенном запросе вообще не вижу условий отбора по этому столбцу. В то, что этот индекс взялся как join для order by a57.val - я не верю.
Скорее план взят не оттуда. Т.к. при order by в плане будет или plan sort или слово order.

Дальше. VAL_STR_IDX5 является "заменителем" отдельных индексов IDX2, IDX3, IDX4, как я понял. Если поиск по столбцам IDX5 присутствует всегда и всегда в виде равенства, то этот индекс действительно полезен. Если нет, то я бы его грохнул, и посмотрел на поведение сервера с индексами 2, 3 и 4.

я уж молчу про то что это база от FB 1.5, а следовательно на таких индексах будут тормоза при сборке мусора, и т.п....

да, VAL_STR_IDX2 - тоже хрень еще та. почитай 99% индекса - один ключ.

Ответить