Firebird - изменился план запроса после backup/restore

[Nicholas]

Случилась у меня такая проблема, после backup и restore базы обнаружил тормоза при выполнении одного запроса, в ходе проверки, выяснилось, что для него изменился план, т.е. в копии базы до backup используется один план а после другой (используется другой индекс для одной из таблиц) причем если написать план ручками, все работает ок, но у меня этот запрос в программе и вообще не понятно откуда ноги растут?
ЗАПРОС:

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

select
t."Wdate",t."Trata_id",kf."Name",kt."Name",
ts."Sum1",v."ValName",ts."Subd",ts."Subk",
c."InOut",c."Name",t."DocNum",t."DocDate",
ts."Remark",t."Remark",eql."Name",eql."Model",t."Sum1",
(select summa from getsummaval(ts."Val_id1",ts."Sum1",2,t."Wdate")),
ts."Kol"
from trataspec ts
join trata t on (t."Trata_id"=ts."Trata_id")
left outer join classif c on t."Classif_id"=c."Classif_id"
left outer join equplist eql on ts."Equpment_id" =eql."Equpment_id"
left outer join pipl kt on (t."KorrToId"=kt."Pipl_id")
left outer join pipl kf on (t."KorrFromId"=kf."Pipl_id")
left outer join val v on(ts."Val_id1"=v."Val_id")
where (ts."Subd"=:Sub or ts."Subk"=:sub) and
((ts."Subk"=:Sub and (t."KorrFromId"=:Pipl_id or :pipl_id =0))
or (ts."Subd"=:Sub and (t."KorrToId"=:Pipl_id or :Pipl_id =0)))
and (eql."Equpment_id"=:Equpment_id or :Equpment_id =0)
and (t."Order_id"=:order_id or :order_id =0)
      and t."Wdate">=:dt1 and t."Wdate"<=:dt2
order by 1,2

изначальный план
PLAN (VK ORDER VALKURS_IDDATE)(KV INDEX (VALKURS_IDVAL))(VK ORDER VALKURS_IDDATE)(KV INDEX (VALKURS_IDVAL))
PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (TS INDEX (TRATASPEC_Subd,TRATASPEC_Subk),T INDEX (PK_TRATA)),C INDEX (PK_CLASSIF)),EQL INDEX (PK_EQUPLIST)),KT INDEX (PK_PIPL)),KF INDEX (PK_PIPL)),V INDEX (PK_VAL)))

план после restore
PLAN (VK ORDER VALKURS_IDDATE)(KV INDEX (VALKURS_IDVAL))(VK ORDER VALKURS_IDDATE)(KV INDEX (VALKURS_IDVAL))
PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (T INDEX (TRATA_IDX1),TS INDEX (FK_TRATASPECHID,TRATASPEC_Subk,TRATASPEC_Subd,TRATASPEC_Subd,TRATASPEC_Subk)),C INDEX (PK_CLASSIF)),EQL INDEX (PK_EQUPLIST)),KT INDEX (PK_PIPL)),KF INDEX (PK_PIPL)),V INDEX (PK_VAL)))

причем удалял индех TRATA_IDX1, он пишет NATURAL, как-будто PK_TRATA вообще не видит

[Модератор: пост отформатирован]

[kdv]

КАКАЯ ВЕРСИЯ СЕРВЕРА?

план может меняться после изменения статистики индексов.
при b/r индексы пересозданы, статистика новая. без б-р, как я понимаю, ты о set statictics и не знал (ну или про IBAnalyst).

[Nicholas]

Сервер WI-V6.3.0.4306 Firebird 1.5
о set statistic не знал до сегодняшнего дня, как и об IBAnalyst, но узнал о них до того как начал писать сообщение статистику паресчитал у всех индексов, проанализоровал IBAnalyst'ом, никакой существенной разницы между копией базы до и базой после b/r не обнаружил. Видать где-то чего-то не догоняю

[Nicholas]

действительно, пересчитал статистику в копии до b/r и получил тормозной план как после b/r, а где можно глянуть, что есть статистика и откуда она берется и как влияет на составление плана?

[kdv]

бывает такое. кардинальность таблиц после b/r изменилась. некая таблица стала "плотнее", т.е. меньше фрагментирована, наверняка.

Насчет запроса - объясни, пожалуйста, зачем тут left join.

[kdv]

а где можно глянуть, что есть статистика и откуда она берется и как влияет на составление плана?

частично - в хелпе IBAnalyst. F1.

[Nicholas]

left join для того что бы были выбраны все записи из trata & trataspec независимо есть ли ссылки на словари(classif, equpment, pipl и т.д.) ведь иначе записи где нет ссылки (напр. Equpment_id is null) будут откинуты, так я понимаю?

кардинальность таблиц после b/r изменилась. некая таблица стала "плотнее", т.е. меньше фрагментирована

а как это влияет на статистику индексов? видимо все-таки придется пририсовать к запросу план руками

[kdv]

иначе записи где нет ссылки (напр. Equpment_id is null) будут откинуты, так я понимаю?

а потом ты эти null убиваешь в where

where (ts."Subd"=:Sub or ts."Subk"=:sub) and
((ts."Subk"=:Sub and (t."KorrFromId"=:Pipl_id or :pipl_id =0))
or (ts."Subd"=:Sub and (t."KorrToId"=:Pipl_id or ipl_id =0)))
and (eql."Equpment_id"=:Equpment_id or :Equpment_id =0)

в результате у тебя никаких null в eql.equipment_id нет.

см. www.ibase.ru/devinfo/joins.htm
разделы "Отличие между ON и WHERE" и особенно "LEFT/RIGHT JOIN" с абзаца "Теперь вспомните, что говорилось выше об отличиях ON и WHERE..."

[WildSery]

Интересные у тебя справочники, на которые можно ссылаться, а можно и нет... Ну да не будем об этом, уже спорил...
Вот тут

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

where (ts."Subd"=:Sub or ts."Subk"=:sub) and 
((ts."Subk"=:Sub and (t."KorrFromId"=:Pipl_id or :pipl_id =0)) 
or (ts."Subd"=:Sub and (t."KorrToId"=:Pipl_id or :Pipl_id =0)))

как-то коряво условие построено, с дублированием.

И kdv верно заметил, для equplist проверку

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

eql."Equpment_id"=:Equpment_id or :Equpment_id =0

следует в join on ... включить

[Nicholas]

запрос из отчета, соответственно если мне нужно выбирать операции по Equpment_id, я в отчет передаю параметр :Equpment_id, если же нет, я передаю :Equpment_id=0 и это условие в выборе не участвует, соотв Equpment_id is null попадает в рез-тат запроса

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

where (ts."Subd"=:Sub or ts."Subk"=:sub) and 
((ts."Subk"=:Sub and (t."KorrFromId"=:Pipl_id or :pipl_id =0)) 
or (ts."Subd"=:Sub and (t."KorrToId"=:Pipl_id or :Pipl_id =0)))

тоже и тут: Subd & Subk - счета, Pipl_id - соотв. контрагент, если меня интересует только счет, передаю его в :sub и ipl_id=0, если нужно выбрать по контрагенту, то интересующий контрагент должен стоять в той-же части (дебет/кредит) где и нтересующий счет, так-что как передашь параметры такой будет и результат

Интересные у тебя справочники, на которые можно ссылаться, а можно и нет... Ну да не будем об этом, уже спорил...

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

[WildSery]

запрос из отчета, соответственно если мне нужно выбирать операции по Equpment_id, я в отчет передаю параметр :Equpment_id, если же нет, я передаю :Equpment_id=0 и это условие в выборе не участвует, соотв Equpment_id is null попадает в рез-тат запроса

Каков тогда глубинный смысл в LEFT JOIN?

По where. Чем твоя конструкция отличается от такой:

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

where (ts."Subk"=:Sub and (t."KorrFromId"=:Pipl_id or :pipl_id =0)) 
or (ts."Subd"=:Sub and (t."KorrToId"=:Pipl_id or :Pipl_id =0))

[Nicholas]

по WHERE согласен, делал в лоб не подумав

Каков тогда глубинный смысл в LEFT JOIN?

а как записи где Equpment_id is null попадут в запрос?

[WildSery]

Действительно, я не о том задумался.
В общем, я подумал о том, что у тебя нельзя отобрать фильтром значения, которые null, только в общей куче. Это так и задумано?

[Nicholas]

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

[Nicholas]

kdv, почитал статейку, пришла мысль, переписал запрос:

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

select
t."Wdate",t."Trata_id",kf."Name",kt."Name",
ts."Sum1",v."ValName",ts."Subd",ts."Subk",
c."InOut",c."Name",t."DocNum",t."DocDate",
ts."Remark",t."Remark",eql."Name",eql."Model",t."Sum1",
(select summa from getsummaval(ts."Val_id1",ts."Sum1",2,t."Wdate")),
ts."Kol"
from trataspec ts
join trata t on (t."Trata_id"=ts."Trata_id" and (ts."Subd"=:Sub or ts."Subk"=:sub))
left outer join classif c on t."Classif_id"=c."Classif_id"
left outer join equplist eql on ts."Equpment_id" =eql."Equpment_id"
left outer join pipl kt on (t."KorrToId"=kt."Pipl_id")
left outer join pipl kf on (t."KorrFromId"=kf."Pipl_id")
left outer join val v on(ts."Val_id1"=v."Val_id")
where ((ts."Subk"=:Sub and (t."KorrFromId"=:Pipl_id or :pipl_id =0))
or (ts."Subd"=:Sub and (t."KorrToId"=:Pipl_id or :Pipl_id =0)))
and (eql."Equpment_id"=:Equpment_id or :Equpment_id =0)
and (t."Order_id"=:order_id or :order_id =0)
      and t."Wdate">=:dt1 and t."Wdate"<=:dt2
order by 1,2

Добавил в первый join условия по счетам (которое было лишним в where) теперь строит хороший план, работает быстро