Странное планирование на Муркоде

KlugCZ

Дата: 05.06.2012 11:52:46

Firebird 2.5.1

Запрос:

select count(op1.oprop_id)as POCET
from   object_props op2
    join objects o2 on o2.object_id=op2.object_id and o2.state_flag=0 and op2.property_id=149 and op2.state_flag=0 and op2.data_to='3'
    join object_tree ot on ot.sol_id=660 and ot.parent_id=o2.object_id
    join objects o on o.object_id=ot.object_id and ot.sol_id=o.csol_id and o.state_flag=0 and o.csol_id=660 and o.class_id=25
    join object_props op1 on o2.object_id=op1.object_id and op1.property_id=109 and op1.state_flag=0 and op1.data is null

Оптимизатор строит план

PLAN JOIN (OP1 INDEX (IDX_OBJECT_PROPS_PSD), O2 INDEX (PK_OBJECTS), OP2 INDEX (IDX_OBJECT_PROPS_OPSD), OT INDEX (IDX_OT_SP), O INDEX (PK_OBJECTS))

И время 57.323s
При том что

FK_OBJECT_PROPS_OBJECTS	ASC	OBJECT_ID	0.000001
FK_OBJECT_PROPS_PROPERTIES	ASC	PROPERTY_ID	0.005882
IDX_OBJECT_PROPS_OPSD	ASC	OBJECT_ID PROPERTY_ID STATE_FLAG DATA	0.000000
IDX_OBJECT_PROPS_PSD	ASC	PROPERTY_ID STATE_FLAG DATA	0.000001
IDX_OP_DATA_TO	ASC	DATA_TO	0.000116
IDX_OP_STATE	ASC	OBJECT_ID STATE_FLAG	0.000001
PK_OBJECT_PROPS	ASC	OPROP_ID	0.000000

Если добавить left

select count(op1.oprop_id)as POCET
from   object_props op2
   join objects o2 on o2.object_id=op2.object_id and o2.state_flag=0 and op2.property_id=149 and op2.state_flag=0 and op2.data_to='3'
   join object_tree ot on ot.sol_id=660 and ot.parent_id=o2.object_id
   join objects o on o.object_id=ot.object_id and ot.sol_id=o.csol_id and o.state_flag=0 and o.csol_id=660 and o.class_id=25
   left join object_props op1 on o2.object_id=op1.object_id and op1.property_id=109 and op1.state_flag=0 
where op1.data is null and op1.oprop_id is not null

То план уже заметно лучше

PLAN JOIN (JOIN (OP2 INDEX (IDX_OP_DATA_TO, FK_OBJECT_PROPS_PROPERTIES), O2 INDEX (PK_OBJECTS), OT INDEX (IDX_OT_SP), O INDEX (PK_OBJECTS)), OP1 INDEX (IDX_OBJECT_PROPS_OPSD))

И время соответственно 1.946s

При попытке вручную написать план

select count(op1.oprop_id)as POCET
from object_props op2
join objects o2 on o2.object_id=op2.object_id and o2.state_flag=0 and op2.property_id=149 and op2.state_flag=0 and op2.data_to='3'
join object_tree ot on ot.sol_id=660 and ot.parent_id=o2.object_id
join objects o on o.object_id=ot.object_id and ot.sol_id=o.csol_id and o.state_flag=0 and o.csol_id=660 and o.class_id=25
join object_props op1 on o2.object_id=op1.object_id and op1.property_id=109 and op1.state_flag=0 
and op1.data is null
PLAN JOIN (JOIN (OP2 INDEX (IDX_OP_DATA_TO, FK_OBJECT_PROPS_PROPERTIES), O2 INDEX (PK_OBJECTS), OT INDEX (IDX_OT_SP), O INDEX (PK_OBJECTS)), OP1 INDEX (IDX_OBJECT_PROPS_OPSD))

То вываливает ошибку: index IDX_OBJECT_PROPS_OPSD cannot be used in the specified plan

Не могу понять почему и как его заставить использовать более оптимальный план.