I have a stored procedure that is extremely slow, but only in Firebird 2.0. In 2.5 it works fine. Here are the performance results. Note - I've removed other tables from the result that were exactly the same.

Table IndexedFB20 IndexedFB25 NonIndexedFB20 NonIndexedFB25
Employee 12181818 968 1350 1350
Header 39744 56592 0 0
Header_Hours 194 1274 17535744 0

As you can see, 2.0 has FAR FAR more indexed and non indexed reads. Resulting time is 171ms on 2.5, and 25756ms on 2.0.

I compared the plans for this using IBExpert and narrowed it to one single statement inside the stored procedure. This statement is the culprit. I took the plan from 2.5 and made it a part of the statement thereby enforcing my own plan. 2.5 works fine. 2.0 gives the error "index RDB$PRIMARY6 cannot be used in the specified plan". This index is specifically on the Employee table.

Here is the SQL statement causing the issue from inside the procedure:
select case when hh.h_department in ('T','R','O') then
hh.h_virtual_available
else hh.h_hours_available
end - coalesce(sum(hh2.h_virtual_total), 0)
from header h
inner join employee e on (((:Department <> 'M') and (h.h_m_techid = e.e_employee_id)) or
((:Department <> 'F') and (h.h_f_techid = e.e_employee_id)) or
((:Department <> 'S') and (h.h_s_techid = e.e_employee_id)) or
((:Department <> 'G') and (h.h_g_techid = e.e_employee_id)) or
((:Department <> 'E') and (h.h_e_techid = e.e_employee_id)) or
((:Department <> 'I') and (h.h_i_techid = e.e_employee_id)) or
((:Department <> 'R') and (h.h_r_techid = e.e_employee_id)) or
((:Department <> 'T') and (h.h_t_techid = e.e_employee_id)))
inner join header_hours hh on hh.h_header_id = h.h_header_id and
e.e_department starting with hh.h_department and
((hh.h_hours_available > 0) or (hh.h_virtual_available > 0)) and
((hh.h_total_hours <> 0) or (hh.h_virtual_total <> 0)) and
hh.h_department <> :Department
left join department d on d.parentcode = hh.h_department
left join header_hours hh2 on hh2.h_header_id = h.h_header_id and hh2.h_department = d.code and
hh2.h_department <> d.parentcode and
((hh2.h_total_hours <> 0) or (hh.h_virtual_total <> 0))
where cast(coalesce(h.h_startdate, h.h_arrivaldate, h.h_scheduled_arrival_date) as date) = :aDate + :counter and
h.h_shopid = :ShopID and
h.h_status in ('Converted to RO','In-Process','Waiting','Parts Ordered','Received','Partially Received') and
e.e_department starting with :Department
group by hh.h_hours_available,
hh.h_virtual_available,
case when hh.h_department in ('T','R','O') then
hh.h_virtual_available
else hh.h_hours_available
end
having hh.h_hours_available + hh.h_virtual_available - coalesce(sum(hh2.h_total_hours), 0) > 0

Here is the plan generated by Firebird 2.0:
PLAN SORT (JOIN (JOIN (JOIN (HH NATURAL, E INDEX (IDX_EMPLOYEE_DEPT), H INDEX (RDB$PRIMARY8)), D INDEX (DEPARTMENT_PARENTCODE_IDX1)), HH2 INDEX (PK_HEADER_HOURS)))

Here is the plan generated by Firebird 2.5:
PLAN SORT (JOIN (JOIN (JOIN (H INDEX (IDX_HEADER_STATUS, IDX_HEADER_STATUS, IDX_HEADER_STATUS, IDX_HEADER_STATUS, IDX_HEADER_STATUS, IDX_HEADER_STATUS), HH INDEX (PK_HEADER_HOURS), E INDEX (RDB$PRIMARY6, RDB$PRIMARY6, RDB$PRIMARY6, RDB$PRIMARY6, RDB$PRIMARY6, RDB$PRIMARY6, RDB$PRIMARY6, RDB$PRIMARY6)), D INDEX (DEPARTMENT_PARENTCODE_IDX1)), HH2 INDEX (PK_HEADER_HOURS)))

When I specify this plan in Firebird 2.0 I get the error mentioned. One last (and very odd) note: If I remove just ONE of the employee join OR clauses, the new plan is generated by Firebird2.0 automatically, and works fine. I still can't specify it manually though! So if the employee join OR area goes from 8 items to 7, all is generated fine, but specifying the plan still fails.

So, how can I specify the correct plan, or fix the query so the optimizer chooses the best plan?