Hi Alexandre,

> I have a query (the * was in the query just to make it short I have a
> column list there) and was intrigued by the choosen plan:

SELECT
*
FROM
Empresa e
JOIN Cliente c ON (e.EmpresaId = c.EmpresaId)
JOIN SegmentoMercado s ON (s.SegmentoMercadoId = c.SegmentoMercadoId)
JOIN Vendedor v ON (v.VendedorId = c.VendedorId)
JOIN Empresa et ON (et.EmpresaId = c.TransportadoraId)
JOIN Empresa ed ON (ed.EmpresaId = c.DistribuidorId)
JOIN ListaPreco lp ON (lp.ListaPrecoId = c.ListaPrecoId)
LEFT JOIN Endereco en ON ((en.EmpresaID = c.EmpresaID) and (en.Tipo = 1))
LEFT JOIN Estado es ON (es.EstadoID = en.EstadoID)
WHERE
e.Nome = 'Alexandre Benson Smith'

> I get this plan:
> PLAN JOIN (JOIN (JOIN (LP NATURAL,C INDEX (FK_CLIENTE_LISTAPRECO),ED
> INDEX (PK_EMPRESA),E INDEX (AK_EMPRESA_NOME),S INDEX
> (PK_SEGMENTOMERCADO),V INDEX (PK_VENDEDOR),ET INDEX (PK_EMPRESA)),EN
> INDEX (FK_EMPRESA_ENDERECO)),ES INDEX (PK_ESTADO))

<snip>

> I have expected the following plan:
>
> PLAN JOIN (JOIN (JOIN (E INDEX (AK_EMPRESA_NOME), C INDEX(PK_CLIENTE),LP
> INDEX (PK_LISTAPRECO),S INDEX (PK_SEGMENTOMERCADO),V INDEX
> (PK_VENDEDOR),ET INDEX (PK_EMPRESA),ED INDEX (PK_EMPRESA)),EN INDEX
> (FK_EMPRESA_ENDERECO)),ES INDEX (PK_ESTADO))
>
> If I force this plan I get far better result and of course just one read
> on the majority of the tables and 3 reads on table Empresa (that is used
> 3 times in this query)
>
> This query returns just one record, and I have a unique index on E.Nome
> that is a varchar(40)

<snip>

> Why the optimizer don't priorize the unique index if I am searching for
> a specific value on that column ???

I'm afraid you hit one of the limitations of the current optimizer (FB1.5).
For some reason (bad estimated values) it has calculated wrong join order, but i
can't explain why because when E is the first table all other relations are
joined by unique indexes which do get a higher priority in the JOIN order
calculation. Are there very small (number of records) tables and large tables in
this query?

Forcing JOIN order can indeed be done by adding + 0 or using LEFT JOIN:

SELECT
*
FROM
Empresa e
JOIN Cliente c ON (c.EmpresaId = e.EmpresaId + 0)
JOIN ListaPreco lp ON (lp.ListaPrecoId = c.ListaPrecoId + 0)
JOIN SegmentoMercado s ON (s.SegmentoMercadoId = c.SegmentoMercadoId + 0)
JOIN Vendedor v ON (v.VendedorId = c.VendedorId + 0)
JOIN Empresa et ON (et.EmpresaId = c.TransportadoraId + 0)
JOIN Empresa ed ON (ed.EmpresaId = c.DistribuidorId + 0)
LEFT JOIN Endereco en ON ((en.EmpresaID = c.EmpresaID) and (en.Tipo = 1))
LEFT JOIN Estado es ON (es.EstadoID = en.EstadoID)
WHERE
e.Nome = 'Alexandre Benson Smith'


The JOIN order calculation has been changed in FB2 and should do a better job.
When you've time you could test if your original query runs fine under FB2 (but
you must restore your database in FB2 to benefit from the optimizer changes).

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info