Это неверное утверждение/вывод, ну по крайней мере вторая часть "не мог использовать индексы по таблице e"

select m.Id
from Items_Main m
left join Items_Ext e on e.Id=m.Id
where e.f1=?p1

PLAN JOIN (M NATURAL, E INDEX (PK_ITEMS_EXT))

Более того этот запрос, в данном случае эквивалентен запросу ... который будет наверное эффективней чем исходный
и план наверное будет где-то такой

PLAN JOIN (E INDEX (.....), M  INDEX (.....))

В WI-V3.0.8.33540 для запроса

select m.Id
from Items_Main m
left join Items_Ext e on e.Id=m.Id
where m.f0=?p0
and e.f1=?p1
and e.f2=?p2
and e.f3=?p3
and e.f4=?p4

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

PLAN JOIN (E INDEX (FK_ITEMS_EXT_REF_F1, FK_ITEMS_EXT_REF_F2), M INDEX (PK_ITEMS_MAIN))

и это стало большой проблемой.

То есть, сервер никогда не использовал индексы по таблице, подключенной через left join, и вдруг начал.

m7m писал(а) Fri, 08 July 2022 09:35

Более того этот запрос, в данном случае эквивалентен запросу ... который будет наверное эффективней чем исходный
и план наверное будет где-то такой

PLAN JOIN (E INDEX (.....), M  INDEX (.....))

Да, эквивалентен, но оптимизатор не умеет его делать если таблица, по которой хороший индекс, приджойнена через left join.
Если считаете, что умеет, покажите тестовый скрипт (создать пару табличек, накидать пару тысяч записей и посмотреть план запроса) - у меня такое не выходит.

Если left join заменить на inner join - тогда да, оптимизатор начинает использовать хороший индекс по E.

select m.Id
from Items_Main m
left join Items_Ext e on e.Id=m.Id
where m.f0=?p0
and e.f1=?p1
and e.f2=?p2
and e.f3=?p3
and e.f4=?p4

select m.Id
from Items_Main m
join Items_Ext e on e.Id=m.Id
where m.f0=?p0
and e.f1=?p1
and e.f2=?p2
and e.f3=?p3
and e.f4=?p4

>>Раньше FB использовал индекс по m.f0, и не мог использовать индексы по таблице e
Это неверное утверждение/вывод, ну по крайней мере вторая часть "не мог использовать индексы по таблице e"

Также я зацепился за утверждение:
m7m писал(а) Thu, 07 July 2022 08:07

>>Раньше FB использовал индекс по m.f0, и не мог использовать индексы по таблице e
Это неверное утверждение/вывод, ну по крайней мере вторая часть "не мог использовать индексы по таблице e"

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

m7m, я не прошу помочь с написанием запроса.
Я здесь, чтобы обсудить факт - поведение оптимизатора существенно менялось, и возможно разработчики СУБД что-то скажут по этому поводу: это временный сбой был, или в будущих версиях они введут эту (в принципе, корректную) оптимизацию, и надо это иметь ввиду.

Да я и не помогаю, я для себя пытаюсь понять задлянафига такое делать.
Сочетание left join и условие в where на ведомую таблицу в моей голове не укладывается

select m.Id
from Items_Main m
left join Items_Ext e on e.Id=m.Id
where m.f0=?p0
and e.f1=?p1
and e.f2=?p2
and e.f3=?p3
and e.f4=?p4

select m.Id
from Items_Main m
left join Items_Ext e on e.Id=m.Id
where e.f1=?p1

не вижу ничего необычного в последних двух планах. left/right всегда выполняются строго "попарно". Поэтому оптимизатор сначала делает left, а потом делает inner. Если же вначале стоят inner, то у оптимизатора уже есть некая свобода действий.
А plan m natural потому что m left join, а дальше идет только условие объединения и фильтрация по e. Сервер никак тут не может использовать индексы по m, просто некуда. А left означает "взять все записи слева". Ну и ...

select ...
from Orders o
left join ... опциональные расширения к заказу в схеме данных
join Cities c on c.Id=o.CityId
where c.Id=?pCity

По-моему, топикстартер забыл нам сказать, что у него не совсем Firebird... а на самом деле HQbird