Индекс Firebird не используется при использовании JOIN, почему?

Я использую FB 2.5.5 и пытаюсь понять, почему очень простой запрос не использует индекс и поэтому выполняется вечно. Я прочитал много статей о том, почему оптимизатор запросов может игнорировать существующие индексы, но я не понимаю, как это может произойти в моем случае. Я пересчитал селективность для всех своих индексов в IB Expert, а также сделал резервную копию/восстановление базы данных, чтобы убедиться, что ничего не упустил.

Селективность индекса, показанная IB Expert, составляет приблизительно 0,000024, что далеко от 1:

CREATE INDEX TVERSIONS_IDX_LASTMODDATE ON TVERSIONS (LASTMODDATE)

Таблица, которую я запрашиваю, содержит ок. 2M записей:

SELECT COUNT(ID) FROM TVERSIONS
2479518

Я пытаюсь получить все записи на основе поля LASTMODDATE (TIMETSAMP, проиндексировано TVERSIONS_IDX_LASTMODDATE). Упрощенная версия запроса будет:

SELECT COUNT(ID) FROM TVERSIONS WHERE LASTMODDATE > :TheDate

В этом случае план выполнения показывает, что индекс действительно используется:

Plan
PLAN (TVERSIONS INDEX (TVERSIONS_IDX_LASTMODDATE))

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

------ Performance info ------
Prepare time = 172ms
Execute time = 16ms <----
Avg fetch time = 16,00 ms
Current memory = 2 714 672
Max memory = 10 128 480
Memory buffers = 90
Reads from disk to cache = 57
Writes from cache to disk = 0
Fetches from cache = 387

Теперь «настоящий» запрос извлекает те же поля с использованием того же условия в LASTMODDATE, но добавляет JOIN к 3 таблицам:

SELECT COUNT(ID) FROM TVERSIONS
    JOIN TFILES ON TFILES.ID = TVERSIONS.FILEID
    JOIN TROOTS ON TROOTS.ID = TFILES.ROOTID
    JOIN TUSERSBACKUPS ON TROOTS.BACKUPID = TUSERSBACKUPS.BACKUPID
    WHERE TUSERSBACKUPS.USERID= :UserID
    AND TVERSIONS.LASTMODDATE >:TheDate

Теперь план запроса больше не использует индекс:

Plan
PLAN JOIN (TUSERSBACKUPS INDEX (RDB$FOREIGN4), TROOTS INDEX (RDB$FOREIGN3), TFILES INDEX (RDB$FOREIGN2), TVERSIONS INDEX (RDB$FOREIGN6))

Без каких-либо неожиданностей время выполнения гораздо медленнее (примерно 1 минута):

------ Performance info ------
Prepare time = 329ms
Execute time = 53s 593ms <---
Avg fetch time = 53 593,00 ms
Current memory = 3 044 736
Max memory = 10 128 480
Memory buffers = 90
Reads from disk to cache = 55 732
Writes from cache to disk = 0
Fetches from cache = 6 952 648

Другими словами, поиск во всей таблице на порядок быстрее, чем поиск в подмножестве строк, возвращаемых JOIN.

Я не могу понять, почему индекс в поле LASTMODDATE больше не используется только потому, что я добавляю предложение соединения. Избирательность индекса хорошая, а запрос очень простой. Что мне не хватает?


firebird firebird2.5
person Adrien Reboisson    schedule 19.02.2016    source источник
comment
Оптимизатор Firebird не всегда делает правильный выбор, вы можете попробовать изменить порядок соединений или использовать (общее) табличное выражение.   -  person Mark Rotteveel    schedule 20.02.2016

Ответы (2)


arrow_upward
2
arrow_downward

Кажется, Firebird решил начать с условия TUSERSBACKUPS.USERID=:UserID, используя индекс RDB$FOREIGN4. Вероятно, это происходит потому, что здесь у вас есть равенство, а для условия TVERSIONS.LASTMODDATE >:TheDate у вас есть неравенство, которое может привести к потенциально большему набору записей (например, если TheDate - это дата 200 лет назад, она будет включать всю таблицу).

Чтобы заставить Firebird использовать план, который предпочитаете вы (но не его оптимизатор), используйте пункт PLAN, см. http://www.firebirdfaq.org/faq224/

person Maxim Votyakov    schedule 20.02.2016

arrow_upward
0
arrow_downward

Думаю, я понял, что произошло, и... Думаю, это была моя вина.

Я забыл, что таблица, которую я запрашиваю, была «денормализована», чтобы избежать таких длинных JOIN. Проблемный запрос действительно можно переписать гораздо короче:

SELECT COUNT(TVERSIONS.ID) FROM TVERSIONS
    JOIN TUSERSBACKUPS ON TUSERSBACKUPS.BACKUPID = TVERSIONS.RD_BACKUPID
    WHERE TUSERSBACKUPS.USERID= :UserID
    AND TVERSIONS.LASTMODDATE >:TheDate

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

У меня сложилось впечатление, что когда Firebird обнаруживает, что вы намеренно используете неоптимальный путь для доступа к записям в таблице, он даже не пытается использовать ваши индексы и позволяет вам выстрелить себе в ногу...

В любом случае проблема решаема. Спасибо всем за ваши предложения.

person Adrien Reboisson    schedule 21.02.2016