Hi guys !

Take a cup of coffe, make your self confortable and keep reading if you
have patience....

This weekend I was looking on some views and trying to optimize it, I
found a way to make it quicker, but don't like the way I get, What I
would like to know is:

Why the optimizer are change the plan so much when I have outer joins ?

Some info:

Firebird 1.5.0.4306 SS on WinXP

Record Count Table
192 ContaContabil
2361 Empresa_CredorDevedor
27796 TituloReceberBaixa
29899 TituloReceber

the other tables are even small...

Here are some samples:

I will try to simplify the query, so I will remove some Columns, but
leave the related Tables (All the envolved tables in the from clause
have columns used in the original query)

Case 1:
the bad guy:
select
trb.TituloReceberBaixaId,
tr.BorderoId,
Bo.Numero
from
TituloReceberBaixa trb join
TituloReceber tr on (trb.TituloReceberId =
tr.TituloReceberId) join
TituloTipo tt on (tr.TituloTipoId =
tt.TituloTipoId) join
Empresa_CredorDevedor ecd on (tr.EmpresaCredorDevedorId =
ecd.Empresa_CredorDevedor_Id) join
ContaContabil cc on (tr.ContaContabilId =
cc.ContaContabilId) join
Moeda m on (tr.MoedaId = m.MoedaId) join
TituloBaixaTipo tbt on (trb.TituloBaixaTipoId =
tbt.TituloBaixaTipoId) join
ContaBancaria cb on (cb.ContaBancariaId =
trb.ContaBancariaId + 0) join
AgenciaBancaria a on (cb.AgenciaBancariaId =
a.AgenciaBancariaId) join
Banco bb on (a.BancoId = bb.BancoId) join
TituloStatus ts1 on (tr.StatusId = ts1.TituloStatusId)
join
TituloStatus ts2 on (trb.StatusId =
ts2.TituloStatusId) join
MultiEmpresa me on (me.MultiEmpresaID =
tr.MultiEmpresaID + 0) left join
Bordero bo on (BorderoID = tr.BorderoID)
where
ecd.Nome like 'CISPL%'
PLAN JOIN (JOIN (TRB NATURAL,TS2 INDEX (RDB$PRIMARY292),TR INDEX
(RDB$PRIMARY296),ME INDEX (RDB$PRIMARY357),TT INDEX (RDB$PRIMARY294),ECD
INDEX (SK_EMPRESA_CREDORDEVEDOR_NOME,RDB$PRIMARY26),CC INDEX
(RDB$PRIMARY17),M INDEX (RDB$PRIMARY41),TBT INDEX (RDB$PRIMARY295),CB
INDEX (RDB$PRIMARY16),A INDEX (RDB$PRIMARY1),BB INDEX (RDB$PRIMARY4),TS1
INDEX (RDB$PRIMARY292)),BO INDEX (RDB$PRIMARY5))

Returns 79 rows
Execution Time: 2284ms
Fetch Time: 13ms


the good guy:
select
trb.TituloReceberBaixaId,
tr.BorderoId,
(Select Numero from Bordero where BorderoID = tr.BorderoID)
from
TituloReceberBaixa trb join
TituloReceber tr on (trb.TituloReceberId =
tr.TituloReceberId) join
TituloTipo tt on (tr.TituloTipoId =
tt.TituloTipoId) join
Empresa_CredorDevedor ecd on (tr.EmpresaCredorDevedorId =
ecd.Empresa_CredorDevedor_Id) join
ContaContabil cc on (tr.ContaContabilId =
cc.ContaContabilId) join
Moeda m on (tr.MoedaId = m.MoedaId) join
TituloBaixaTipo tbt on (trb.TituloBaixaTipoId =
tbt.TituloBaixaTipoId) join
ContaBancaria cb on (cb.ContaBancariaId =
trb.ContaBancariaId + 0) join
AgenciaBancaria a on (cb.AgenciaBancariaId =
a.AgenciaBancariaId) join
Banco bb on (a.BancoId = bb.BancoId) join
TituloStatus ts1 on (tr.StatusId = ts1.TituloStatusId)
join
TituloStatus ts2 on (trb.StatusId =
ts2.TituloStatusId) join
MultiEmpresa me on (me.MultiEmpresaID =
tr.MultiEmpresaID + 0)
where
ecd.Nome like 'CISPL%'

PLAN (BORDERO INDEX (RDB$PRIMARY5))
PLAN JOIN (ECD INDEX (SK_EMPRESA_CREDORDEVEDOR_NOME),TR INDEX
(RDB$FOREIGN298),ME INDEX (RDB$PRIMARY357),TRB INDEX
(RDB$FOREIGN305),TS2 INDEX (RDB$PRIMARY292),TT INDEX (RDB$PRIMARY294),CC
INDEX (RDB$PRIMARY17),M INDEX (RDB$PRIMARY41),TBT INDEX
(RDB$PRIMARY295),CB INDEX (RDB$PRIMARY16),A INDEX (RDB$PRIMARY1),BB
INDEX (RDB$PRIMARY4),TS1 INDEX (RDB$PRIMARY292))

Returns 79 rows
Execution Time: 15ms
Fetch Time: 13ms


If I convert the outer join to an inner join (of course will be faster,
but I was not expecting a so huge difference, and don't undersant a plan
so diferent)

select
trb.TituloReceberBaixaId,
tr.BorderoId,
Bo.Numero
from
TituloReceberBaixa trb join
TituloReceber tr on (trb.TituloReceberId =
tr.TituloReceberId) join
TituloTipo tt on (tr.TituloTipoId =
tt.TituloTipoId) join
Empresa_CredorDevedor ecd on (tr.EmpresaCredorDevedorId =
ecd.Empresa_CredorDevedor_Id) join
ContaContabil cc on (tr.ContaContabilId =
cc.ContaContabilId) join
Moeda m on (tr.MoedaId = m.MoedaId) join
TituloBaixaTipo tbt on (trb.TituloBaixaTipoId =
tbt.TituloBaixaTipoId) join
ContaBancaria cb on (cb.ContaBancariaId =
trb.ContaBancariaId + 0) join
AgenciaBancaria a on (cb.AgenciaBancariaId =
a.AgenciaBancariaId) join
Banco bb on (a.BancoId = bb.BancoId) join
TituloStatus ts1 on (tr.StatusId = ts1.TituloStatusId)
join
TituloStatus ts2 on (trb.StatusId =
ts2.TituloStatusId) join
MultiEmpresa me on (me.MultiEmpresaID =
tr.MultiEmpresaID + 0) join
Bordero bo on (BorderoID = tr.BorderoID)
where
ecd.Nome like 'CISPL%'

PLAN JOIN (ECD INDEX (SK_EMPRESA_CREDORDEVEDOR_NOME),TR INDEX
(RDB$FOREIGN298),BO INDEX (RDB$PRIMARY5),TRB INDEX (RDB$FOREIGN305),TS2
INDEX (RDB$PRIMARY292),TT INDEX (RDB$PRIMARY294),CC INDEX
(RDB$PRIMARY17),M INDEX (RDB$PRIMARY41),TBT INDEX (RDB$PRIMARY295),CB
INDEX (RDB$PRIMARY16),A INDEX (RDB$PRIMARY1),BB INDEX (RDB$PRIMARY4),TS1
INDEX (RDB$PRIMARY292),ME INDEX (RDB$PRIMARY357))

Returns 5 rows
Execution Time: 6ms
Fetch Time: 12ms


if I remove the last table (the one that have the outer join) I got a
plan very similar to the "good" one, and something similar to what I was
expecting:

select
trb.TituloReceberBaixaId,
tr.BorderoId
from
TituloReceberBaixa trb join
TituloReceber tr on (trb.TituloReceberId =
tr.TituloReceberId) join
TituloTipo tt on (tr.TituloTipoId =
tt.TituloTipoId) join
Empresa_CredorDevedor ecd on (tr.EmpresaCredorDevedorId =
ecd.Empresa_CredorDevedor_Id) join
ContaContabil cc on (tr.ContaContabilId =
cc.ContaContabilId) join
Moeda m on (tr.MoedaId = m.MoedaId) join
TituloBaixaTipo tbt on (trb.TituloBaixaTipoId =
tbt.TituloBaixaTipoId) join
ContaBancaria cb on (cb.ContaBancariaId =
trb.ContaBancariaId + 0) join
AgenciaBancaria a on (cb.AgenciaBancariaId =
a.AgenciaBancariaId) join
Banco bb on (a.BancoId = bb.BancoId) join
TituloStatus ts1 on (tr.StatusId = ts1.TituloStatusId)
join
TituloStatus ts2 on (trb.StatusId =
ts2.TituloStatusId) join
MultiEmpresa me on (me.MultiEmpresaID =
tr.MultiEmpresaID + 0)
where
ecd.Nome like 'CISPL%'
PLAN JOIN (ECD INDEX (SK_EMPRESA_CREDORDEVEDOR_NOME),TR INDEX
(RDB$FOREIGN298),ME INDEX (RDB$PRIMARY357),TRB INDEX
(RDB$FOREIGN305),TS2 INDEX (RDB$PRIMARY292),TT INDEX (RDB$PRIMARY294),CC
INDEX (RDB$PRIMARY17),M INDEX (RDB$PRIMARY41),TBT INDEX
(RDB$PRIMARY295),CB INDEX (RDB$PRIMARY16),A INDEX (RDB$PRIMARY1),BB
INDEX (RDB$PRIMARY4),TS1 INDEX (RDB$PRIMARY292))

Returns 79 rows
Execution Time: 13ms
Fetch Time: 12ms



Case 2:
the bad guy:
select
tr.Numero,
tr.Parcela,
tr.EmpresaCredorDevedorId,
ecd.Nome,
ecd.Referencia,
tr.BorderoId,
bo.Numero,
bo.ContaBancariaID,
cb.Numero,
ag.BancoID,
ba.Nome
from
TituloReceber tr join
TituloTipo tt on (tt.TituloTipoId = tr.TituloTipoId) join
Empresa_CredorDevedor ecd on (ecd.Empresa_CredorDevedor_Id =
tr.EmpresaCredorDevedorId) join
ContaContabil cc on (cc.ContaContabilId =
tr.ContaContabilId) join
Moeda m on (m.MoedaId = tr.MoedaId) join
Usuario u on (u.UsuarioId = tr.UsuarioId) join
TituloStatus ts on (ts.TituloStatusId = tr.StatusId) join
MultiEmpresa me on (me.MultiEmpresaID =
tr.MultiEmpresaID + 0) left join
Bordero bo on (bo.BorderoID = tr.BorderoID) left join
ContaBancaria cb on (cb.ContabancariaID =
bo.ContaBancariaID) left join
AgenciaBancaria ag on (ag.AgenciaBancariaID =
cb.AgenciaBancariaID) left join
Banco ba on (ba.BancoID = ag.BancoID)
where
ecd.Nome like 'CISPLA%'

PLAN JOIN (JOIN (JOIN (JOIN (JOIN (TR NATURAL,ME INDEX
(RDB$PRIMARY357),TT INDEX (RDB$PRIMARY294),ECD INDEX
(SK_EMPRESA_CREDORDEVEDOR_NOME,RDB$PRIMARY26),CC INDEX (RDB$PRIMARY17),M
INDEX (RDB$PRIMARY41),U INDEX (RDB$PRIMARY91),TS INDEX
(RDB$PRIMARY292)),BO INDEX (RDB$PRIMARY5)),CB INDEX (RDB$PRIMARY16)),AG
INDEX (RDB$PRIMARY1)),BA INDEX (RDB$PRIMARY4))


Returns 81 rows
Execution Time: 1120ms
Fetch Time: 411ms



the good guy:
select
tr.Numero,
tr.Parcela,
tr.EmpresaCredorDevedorId,
ecd.Nome,
ecd.Referencia,
tr.BorderoId,
(Select bo.Numero from Bordero bo where bo.BorderoID = tr.BorderoID),
(Select bo.ContaBancariaID from Bordero bo where bo.BorderoID =
tr.BorderoID),
(Select cb.Numero from ContaBancaria cb join Bordero bo on
(cb.ContaBancariaID = bo.ContabancariaID) where bo.BorderoID =
tr.BorderoID),
(Select ag.BancoID from AgenciaBancaria ag join ContaBancaria cb
on (cb.AgenciaBancariaID = ag.AgenciaBancariaID) join Bordero bo on
(bo.ContaBancariaID = cb.ContabancariaID) where bo.BorderoID =
tr.BorderoID),
(Select ba.Nome from Banco ba join AgenciaBancaria ag on
(ag.BancoID = ba.BancoID) join ContaBancaria cb on (cb.AgenciaBancariaID
= ag.AgenciaBancariaID) join Bordero bo on (bo.ContaBancariaID =
cb.ContabancariaID) where bo.BorderoID = tr.BorderoID)
from
TituloReceber tr join
TituloTipo tt on (tr.TituloTipoId = tt.TituloTipoId) join
Empresa_CredorDevedor ecd on (tr.EmpresaCredorDevedorId =
ecd.Empresa_CredorDevedor_Id) join
ContaContabil cc on (tr.ContaContabilId =
cc.ContaContabilId) join
Moeda m on (tr.MoedaId = m.MoedaId) join
Usuario u on (tr.UsuarioId = u.UsuarioId) join
TituloStatus ts on (tr.StatusId = ts.TituloStatusId) join
MultiEmpresa me on (tr.MultiEmpresaID + 0 =
me.MultiEmpresaID)
where
ecd.Nome like 'CISPLA%'

PLAN JOIN (BO INDEX (RDB$PRIMARY5),CB INDEX (RDB$PRIMARY16),AG INDEX
(RDB$PRIMARY1),BA INDEX (RDB$PRIMARY4))
PLAN JOIN (BO INDEX (RDB$PRIMARY5),CB INDEX (RDB$PRIMARY16),AG INDEX
(RDB$PRIMARY1))
PLAN JOIN (BO INDEX (RDB$PRIMARY5),CB INDEX (RDB$PRIMARY16))
PLAN (BO INDEX (RDB$PRIMARY5))
PLAN (BO INDEX (RDB$PRIMARY5))
PLAN JOIN (ECD INDEX (SK_EMPRESA_CREDORDEVEDOR_NOME),TR INDEX
(RDB$FOREIGN298),ME INDEX (RDB$PRIMARY357),TT INDEX (RDB$PRIMARY294),CC
INDEX (RDB$PRIMARY17),M INDEX (RDB$PRIMARY41),U INDEX (RDB$PRIMARY91),TS
INDEX (RDB$PRIMARY292))


Returns 81 rows
Execution Time: 17ms
Fetch Time: 16ms


I have some of other ones that I have increased the speed in the same
magnitude as the above samples...

Another case (a bit different), I took the "last good guy" query and add
another check in the where, this brings the speed down considerabily:
select
tr.Numero,
tr.Parcela,
tr.EmpresaCredorDevedorId,
ecd.Nome,
ecd.Referencia,
tr.BorderoId,
(Select bo.Numero from Bordero bo where bo.BorderoID = tr.BorderoID),
(Select bo.ContaBancariaID from Bordero bo where bo.BorderoID =
tr.BorderoID),
(Select cb.Numero from ContaBancaria cb join Bordero bo on
(cb.ContaBancariaID = bo.ContabancariaID) where bo.BorderoID =
tr.BorderoID),
(Select ag.BancoID from AgenciaBancaria ag join ContaBancaria cb
on (cb.AgenciaBancariaID = ag.AgenciaBancariaID) join Bordero bo on
(bo.ContaBancariaID = cb.ContabancariaID) where bo.BorderoID =
tr.BorderoID),
(Select ba.Nome from Banco ba join AgenciaBancaria ag on
(ag.BancoID = ba.BancoID) join ContaBancaria cb on (cb.AgenciaBancariaID
= ag.AgenciaBancariaID) join Bordero bo on (bo.ContaBancariaID =
cb.ContabancariaID) where bo.BorderoID = tr.BorderoID)
from
TituloReceber tr join
TituloTipo tt on (tr.TituloTipoId = tt.TituloTipoId) join
Empresa_CredorDevedor ecd on (tr.EmpresaCredorDevedorId =
ecd.Empresa_CredorDevedor_Id) join
ContaContabil cc on (tr.ContaContabilId =
cc.ContaContabilId) join
Moeda m on (tr.MoedaId = m.MoedaId) join
Usuario u on (tr.UsuarioId = u.UsuarioId) join
TituloStatus ts on (tr.StatusId = ts.TituloStatusId) join
MultiEmpresa me on (tr.MultiEmpresaID + 0 =
me.MultiEmpresaID)
where
ecd.Nome like 'CISPLA%' and
tr.MultiEmpresaID = 1;

PLAN JOIN (BO INDEX (RDB$PRIMARY5),CB INDEX (RDB$PRIMARY16),AG INDEX
(RDB$PRIMARY1),BA INDEX (RDB$PRIMARY4))
PLAN JOIN (BO INDEX (RDB$PRIMARY5),CB INDEX (RDB$PRIMARY16),AG INDEX
(RDB$PRIMARY1))
PLAN JOIN (BO INDEX (RDB$PRIMARY5),CB INDEX (RDB$PRIMARY16))
PLAN (BO INDEX (RDB$PRIMARY5))
PLAN (BO INDEX (RDB$PRIMARY5))
PLAN JOIN (TR INDEX (FK_TITULORECEBER_MEMPRESA),ME INDEX
(RDB$PRIMARY357),TT INDEX (RDB$PRIMARY294),ECD INDEX
(SK_EMPRESA_CREDORDEVEDOR_NOME,RDB$PRIMARY26),CC INDEX (RDB$PRIMARY17),M
INDEX (RDB$PRIMARY41),U INDEX (RDB$PRIMARY91),TS INDEX (RDB$PRIMARY292))

Returns 81 rows
Execution Time: 1150ms
Fetch Time: 422ms

tr.MultiEmpresaID is a column that got indexed because of a FK, this is
a VERY BAD index (I still prefer to have declarative FK constraints on
create table statment, instead of enforce it via triggers, I tend to
disable the indexes on views adding +0 to the column). When FB permits
to create FK's without indexes I will be happy... ;-)

So I think the bad performance is caused by this index
(FK_TITULORECEBER_MEMPRESA) so I changed it to:
select
tr.Numero,
tr.Parcela,
tr.EmpresaCredorDevedorId,
ecd.Nome,
ecd.Referencia,
tr.BorderoId,
(Select bo.Numero from Bordero bo where bo.BorderoID = tr.BorderoID),
(Select bo.ContaBancariaID from Bordero bo where bo.BorderoID =
tr.BorderoID),
(Select cb.Numero from ContaBancaria cb join Bordero bo on
(cb.ContaBancariaID = bo.ContabancariaID) where bo.BorderoID =
tr.BorderoID),
(Select ag.BancoID from AgenciaBancaria ag join ContaBancaria cb
on (cb.AgenciaBancariaID = ag.AgenciaBancariaID) join Bordero bo on
(bo.ContaBancariaID = cb.ContabancariaID) where bo.BorderoID =
tr.BorderoID),
(Select ba.Nome from Banco ba join AgenciaBancaria ag on
(ag.BancoID = ba.BancoID) join ContaBancaria cb on (cb.AgenciaBancariaID
= ag.AgenciaBancariaID) join Bordero bo on (bo.ContaBancariaID =
cb.ContabancariaID) where bo.BorderoID = tr.BorderoID)
from
TituloReceber tr join
TituloTipo tt on (tr.TituloTipoId = tt.TituloTipoId) join
Empresa_CredorDevedor ecd on (tr.EmpresaCredorDevedorId =
ecd.Empresa_CredorDevedor_Id) join
ContaContabil cc on (tr.ContaContabilId =
cc.ContaContabilId) join
Moeda m on (tr.MoedaId = m.MoedaId) join
Usuario u on (tr.UsuarioId = u.UsuarioId) join
TituloStatus ts on (tr.StatusId = ts.TituloStatusId) join
MultiEmpresa me on (tr.MultiEmpresaID + 0 =
me.MultiEmpresaID)
where
ecd.Nome like 'CISPLA%' and
tr.MultiEmpresaID + 0 = 1;

PLAN JOIN (BO INDEX (RDB$PRIMARY5),CB INDEX (RDB$PRIMARY16),AG INDEX
(RDB$PRIMARY1),BA INDEX (RDB$PRIMARY4))
PLAN JOIN (BO INDEX (RDB$PRIMARY5),CB INDEX (RDB$PRIMARY16),AG INDEX
(RDB$PRIMARY1))
PLAN JOIN (BO INDEX (RDB$PRIMARY5),CB INDEX (RDB$PRIMARY16))
PLAN (BO INDEX (RDB$PRIMARY5))
PLAN (BO INDEX (RDB$PRIMARY5))
PLAN JOIN (TR NATURAL,ME INDEX (RDB$PRIMARY357),TT INDEX
(RDB$PRIMARY294),ECD INDEX
(SK_EMPRESA_CREDORDEVEDOR_NOME,RDB$PRIMARY26),CC INDEX (RDB$PRIMARY17),M
INDEX (RDB$PRIMARY41),U INDEX (RDB$PRIMARY91),TS INDEX (RDB$PRIMARY292))

Returns 81 rows
Execution Time: 1138ms
Fetch Time: 424ms

This puzzled me, why even when I disable the optimizer to choose the bad
index they choose a very diferent plan from the good one, so I create a
tempo Column called MEmpresaID on Table TituloReceber (the tr on teh
above statments), did a update TituloReceber set MEmpresaID =
MultiEmpresaID, and did NOT create any indexes on that column, and did
the following:
select
tr.Numero,
tr.Parcela,
tr.EmpresaCredorDevedorId,
ecd.Nome,
ecd.Referencia,
tr.BorderoId,
(Select bo.Numero from Bordero bo where bo.BorderoID = tr.BorderoID),
(Select bo.ContaBancariaID from Bordero bo where bo.BorderoID =
tr.BorderoID),
(Select cb.Numero from ContaBancaria cb join Bordero bo on
(cb.ContaBancariaID = bo.ContabancariaID) where bo.BorderoID =
tr.BorderoID),
(Select ag.BancoID from AgenciaBancaria ag join ContaBancaria cb
on (cb.AgenciaBancariaID = ag.AgenciaBancariaID) join Bordero bo on
(bo.ContaBancariaID = cb.ContabancariaID) where bo.BorderoID =
tr.BorderoID),
(Select ba.Nome from Banco ba join AgenciaBancaria ag on
(ag.BancoID = ba.BancoID) join ContaBancaria cb on (cb.AgenciaBancariaID
= ag.AgenciaBancariaID) join Bordero bo on (bo.ContaBancariaID =
cb.ContabancariaID) where bo.BorderoID = tr.BorderoID)
from
TituloReceber tr join
TituloTipo tt on (tr.TituloTipoId = tt.TituloTipoId) join
Empresa_CredorDevedor ecd on (tr.EmpresaCredorDevedorId =
ecd.Empresa_CredorDevedor_Id) join
ContaContabil cc on (tr.ContaContabilId =
cc.ContaContabilId) join
Moeda m on (tr.MoedaId = m.MoedaId) join
Usuario u on (tr.UsuarioId = u.UsuarioId) join
TituloStatus ts on (tr.StatusId = ts.TituloStatusId) join
MultiEmpresa me on (tr.MEmpresaID = me.MultiEmpresaID)
where
ecd.Nome like 'CISPLA%' and
tr.MEmpresaID = 1;

PLAN JOIN (BO INDEX (RDB$PRIMARY5),CB INDEX (RDB$PRIMARY16),AG INDEX
(RDB$PRIMARY1),BA INDEX (RDB$PRIMARY4))
PLAN JOIN (BO INDEX (RDB$PRIMARY5),CB INDEX (RDB$PRIMARY16),AG INDEX
(RDB$PRIMARY1))
PLAN JOIN (BO INDEX (RDB$PRIMARY5),CB INDEX (RDB$PRIMARY16))
PLAN (BO INDEX (RDB$PRIMARY5))
PLAN (BO INDEX (RDB$PRIMARY5))
PLAN JOIN (TR NATURAL,ME INDEX (RDB$PRIMARY357),TT INDEX
(RDB$PRIMARY294),ECD INDEX
(SK_EMPRESA_CREDORDEVEDOR_NOME,RDB$PRIMARY26),CC INDEX (RDB$PRIMARY17),M
INDEX (RDB$PRIMARY41),U INDEX (RDB$PRIMARY91),TS INDEX (RDB$PRIMARY292))

Returns 81 rows
Execution Time: 1108ms
Fetch Time: 413ms

Note that I have not mention the column with the bad index, and only the
new one without index...

I have a simple example of the same query:
select
tr.Numero,
tr.Parcela,
tr.EmpresaCredorDevedorId,
ecd.Nome,
ecd.Referencia,
tr.BorderoId
from
TituloReceber tr join
TituloTipo tt on (tr.TituloTipoId = tt.TituloTipoId) join
Empresa_CredorDevedor ecd on (tr.EmpresaCredorDevedorId =
ecd.Empresa_CredorDevedor_Id) join
ContaContabil cc on (tr.ContaContabilId =
cc.ContaContabilId) join
Moeda m on (tr.MoedaId = m.MoedaId) join
Usuario u on (tr.UsuarioId = u.UsuarioId) join
TituloStatus ts on (tr.StatusId = ts.TituloStatusId) join
MultiEmpresa me on (tr.MEmpresaID = me.MultiEmpresaID)
where
ecd.Nome like 'CISPLA%' and
tr.MEmpresaID = 1;

PLAN JOIN (TR NATURAL,ME INDEX (RDB$PRIMARY357),TT INDEX
(RDB$PRIMARY294),ECD INDEX
(SK_EMPRESA_CREDORDEVEDOR_NOME,RDB$PRIMARY26),CC INDEX (RDB$PRIMARY17),M
INDEX (RDB$PRIMARY41),U INDEX (RDB$PRIMARY91),TS INDEX (RDB$PRIMARY292))

Returns 81 rows
Execution Time: 1300ms
Fetch Time: 193ms


Why did the optmizer choose a very diferent approach, and did not start
from the Empresa_CredorDevedor (ecd) table using index
SK_EMPRESA_CREDORDEVEDOR_NOME like on the "good guy" case...

I can provide more information (indexes statistics) if you wish (I just
don't post because this message was already long), or provide a vnc
session or a temporary FB access trough internet, if someone wish to
play with this data...

thank you very much for your patience....

see you !

--

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br