Ограничение времени выполнения запроса

CyberMax · Сообщение **CyberMax** » 10 июн 2006, 18:12

pticelov писал(а):2 cybermax
переделка запроса с явным указанием join изменила план и исправила время выполнения (работает мгновенно)

Отлично.
2 dimitr: с тебя поллитра

pticelov · Сообщение **pticelov** » 10 июн 2006, 19:10

Индекс address_00_1 - по полю link

Получается, что при этом выборка по улицам делается уже без индекса.

pticelov · Сообщение **pticelov** » 10 июн 2006, 19:28

Продолжаю исследование. Удалил индекс person2addr1_1 (по двум полям), остались индексы по обоим полям отдельные.

Запрос: мой оригинальный

план:

PLAN JOIN (PERSON INDEX (PERSON1_1,PERSON_BIRTHDAY_1),ADDRESS INDEX (ADDRESS_STREET_1),P2A INDEX (PERSON2ADDR_PERSON_1,PERSON2ADDR_ADDR_1))

что мы видим? объединенный индекс заменился двумя отдельными. И все. Кажется, должно быть только медленней. А на практике - результат совсем другой: вместо 30 минут выполнения со 100% загрузкой процессора получили 40 секунд при первом запуске (сразу после перезапуска сервера, кеш пустой), 2 секунды - при втором.

Может это ен мои кривые руки виноваты?

pticelov · Сообщение **pticelov** » 10 июн 2006, 20:09

Обещанная статистика по индексам:

Index PERSON1_1 (1)
Depth: 3, leaf buckets: 10744, nodes: 14283537
Average data length: 1.00, total dup: 4940865, max dup: 464

Index PERSON_BIRTHDAY_1 (5)
Depth: 3, leaf buckets: 5251, nodes: 14283537
Average data length: 0.00, total dup: 14245546, max dup: 2127288

Index ADDRESS_STREET_1 (3)
Depth: 2, leaf buckets: 1403, nodes: 3739938
Average data length: 0.00, total dup: 3731319, max dup: 115646

Index PERSON2ADDR_1_1 (0)
Depth: 3, leaf buckets: 18872, nodes: 13379378
Average data length: 7.00, total dup: 0, max dup: 0

Index PERSON2ADDR_ADDR_1 (2)
Depth: 3, leaf buckets: 5661, nodes: 13379378
Average data length: 0.00, total dup: 9685490, max dup: 21441

Index PERSON2ADDR_PERSON_1 (1)
Depth: 3, leaf buckets: 5620, nodes: 13379378
Average data length: 0.00, total dup: 3423877, max dup: 460

CyberMax · Сообщение **CyberMax** » 11 июн 2006, 15:27

1. Кто тебя так учил индексы именовать? Наименование индекса ДОЛЖНО включать в себя наименование таблицы и по каким полям сделано. Вот скажи, глядя на названия, PERSON1_1 и PERSON2ADDR_1_1 какие поля индексируют?
2. По поводу ускорения от разделения индекса - вполне нормальная вещь. "О, сколько открытый чудных, нам предстоит" (С) Не помню

pticelov · Сообщение **pticelov** » 11 июн 2006, 17:20

Согласен про наименование, это были вспомогательные индексы, сделанные на скору руку, и их я обычно цифрами обозначаю.

А вот про разделение индексов нельзя ли подробней? Я всегда был уверен в прямо обратном: при поиске по двум ключам с "and" комбинированный индекс предпочтительней, чем 2 раздельных. И файрбирд при наличии выбора (раздельные индексы и комбинированный) выбирает комбинированный. В памяти всплыл mysql, для которого индекс по нескольким полям - вообще единственный выход, поскольку он более одного индекса в запросе использовать не желает. А тут получается прямо обратный вывод.

У меня такое ощущение, что я на какую-то багу напоролся.

CyberMax · Сообщение **CyberMax** » 12 июн 2006, 11:55

pticelov писал(а):А вот про разделение индексов нельзя ли подробней? Я всегда был уверен в прямо обратном: при поиске по двум ключам с "and" комбинированный индекс предпочтительней, чем 2 раздельных. И файрбирд при наличии выбора (раздельные индексы и комбинированный) выбирает комбинированный.

Меня могут поправить, но на мой взгляд ситуация выглядит следующим образом:
1. Указывая left join, ты говоришь серверу, что эту таблицу надо присоединять только к записям основной таблицы, которые были отобраны по ее условиям.
2. Пример. У тебя для базовой таблицы индекс по трем полям: Фамилия, Имя и Отчество. Если у тебя индекс стоит по ФИО, то серверу приходится извлекать этот огромный индекс и по нему отбирать условие person.fname=450. Из оставшейся кучки записей он (вероятно) индексированным поиском переберет записи на person.pname=344 и person.sname=71921. Если у тебя индекс стоит только по Ф, то он быстро пробежится по нему (индекс-то в три раза легче!), натуралом проверит оставшиеся условия и все. После приджойнит таблицы, проверит их условия и набор готов.
3. Если ты джойнишь через where, оптимизатор может неверно определить, с какой таблицы начинать проверку. Например, сервер начнет проверять не person, а address. То есть сначала отберет по adrress.street. А так как там у тебя условие IN, это может надолго затянуться... Следующим шагом, он к полученному набору приджойнит Person. А в конце выполнит условия для Person по Ф, И и О.

Видишь разницу? Не факт, конечно, что все пойдет по худшему сценарию, но вероятность есть. Поэтому серверу надо всячески помогать, скармливая "правильные" запросы.

pticelov писал(а):В памяти всплыл mysql, для которого индекс по нескольким полям - вообще единственный выход, поскольку он более одного индекса в запросе использовать не желает. А тут получается прямо обратный вывод.

Не всегда одиночные индексы лучше... Например, для быстрой сортировки по ФИО, нужен индекс по ФИО...

pticelov писал(а):У меня такое ощущение, что я на какую-то багу напоролся.

В смысле на багу?

dimitr · Сообщение **dimitr** » 12 июн 2006, 12:41

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

так точно, если используется поиск по равенству. Иначе есть нюансы.

pticelov · Сообщение **pticelov** » 13 июн 2006, 02:34

2 cybermax

фокус в том, что у меня таблицы person и address примерно равны. Какая из них главная - зависит явно от значений входных. Проверка по ФИО может дать 10000 результатов. по адресу - 1. И наоборот. Пусть оптимизирует оптимизатор.

Почему я думаю о баге: потому что файрбирд явно зацикливался в каком-то неудачном алгоритме на неестественно длинное время. Даже без обращения к диску - памяти хватало. Отсюда и 100% пождирание ресурсов. Я оставил свой старый запрос и просто удалил комбинированный индекс. План остался прежний, только вместо одного индекса мы используем 2. А результат - заработало без тормозов, вместо десятков минут - единицы секунд на тех же входных значениях. Вот это похоже на багу.

2 dmitr. Согласен. Но у меня-то "равно".

dimitr · Сообщение **dimitr** » 13 июн 2006, 08:36

если есть подозрение на багу, то надо готовить тестовый пример (база + запрос) и высылать разработчикам. Диагноз получишь в течении суток.

pticelov · Сообщение **pticelov** » 13 июн 2006, 13:40

База 9Г

Сделать генератор - облом. Глюк возникает при определенных значениях входных параметров. Может в отладчике что посмотреть?

dimitr · Сообщение **dimitr** » 13 июн 2006, 14:22

вкурил еще раз тему. Пример не нужен. Насколько я помню, эта проблема с композитами и "волшебными значениями" устранена в 2.0.

CyberMax · Сообщение **CyberMax** » 13 июн 2006, 16:51

pticelov писал(а): Проверка по ФИО может дать 10000 результатов. по адресу - 1. И наоборот. Пусть оптимизирует оптимизатор.

Неправда!
Сколько в городе людей, имеющих одинаковое ФИО? С десяток от силы, если брать мегаполис. А вот людей, живущих на одной улице, больше как раз эдак в 10 000... Да и вообще, глупо полагаться на оптимизатор. Если ты сам не можешь свой запрос оптимизировать, что можно требовать от железяки?

Сообщение **kdv** » 13 июн 2006, 17:21

Неправда!

ну, тут мораль такая - гистограмм распределения значений в индексе у нас нет, да и для строк это сложновато (разве что первую букву можно использовать). Поэтому у оптимизатора есть только оценка распределения в виде числа повторов ключей (Total Dup) и максимального числа ключей в одной группе (Max Dup). А поскольку распределение групп одинаковых ключей среднестатистическим не бывает, оптимизатор, разумеется, не может понять, результатом выборки по ключу будет 1 ключ (минимальный вариант) или MaxDup ключей (максимальный вариант). Это если на равенство проверять.
Вот...

pticelov · Сообщение **pticelov** » 13 июн 2006, 19:17

2 kdv:

честное слово, я не такой глупый, чтобы там использовать строки:фамилия, имя, отчество - только integer.

Что при таком раскладе оценивает оптимизатор?

2 cybermax:

в заросе може быть не только ФИО, а, к примеру, просто имя. "Александр"

А в адресе - не только улица, а цлица + дом + квартира

Сообщение **kdv** » 13 июн 2006, 19:32

pticelov писал(а):честное слово, я не такой глупый,

я отвечал Cybermax.

pticelov писал(а):Что при таком раскладе оценивает оптимизатор?

то же самое. скачай IBAnalyst, открой хелп. Прочитай про total dup, max dup, селективность, а также в Доп. вопросах и ответах пункт 7.

CyberMax · Сообщение **CyberMax** » 14 июн 2006, 01:40

pticelov писал(а):в заросе може быть не только ФИО, а, к примеру, просто имя. "Александр" А в адресе - не только улица, а цлица + дом + квартира

Речь шла о конкретном запросе (вышеупомянутом).
В твоем последнем случае запрос будет просто другой.

2 kdv. Исчерпывающее объяснение. Спасибо.