>I have been going nuts trying to figure this out...Ill just write the queries and results Im getting.
>All tables on JOINS are referencing their PKs and FKS (properly created/indexed) :
>
>tables overview :
>
>HD_OCORRENCIAS - 500.000 rows (calls/incidents table)
>HD_STATUS - 10 rows (calls status table)
>ORDEM_SERVICO_DIVISAO - 20 rows (calls support areas table)
>HD_PROBLEMAS - 250 rows (types of calls table)
>CLIENTES - 200.000 rows (clients table)
>
>very slow query (the one I need to run on the system) :
>
>SELECT
>HD_OCORRENCIAS.NUMERO
>FROM HD_OCORRENCIAS
>INNER JOIN HD_STATUS ON HD_OCORRENCIAS.STATUS = HD_STATUS.STAT_ID
>INNER JOIN ORDEM_SERVICO_DIVISAO ON HD_OCORRENCIAS.SISTEMA = ORDEM_SERVICO_DIVISAO.IDDIVISAO
>INNER JOIN HD_PROBLEMAS ON HD_OCORRENCIAS.PROBLEMA = HD_PROBLEMAS.PROB_ID
>INNER JOIN CLIENTES ON HD_OCORRENCIAS.IDCLIENTE = CLIENTES.IDCLIENTE
>WHERE (HD_STATUS.STAT_PAINEL = 2)
>
>plan used :
>PLAN JOIN (CLIENTES NATURAL, HD_OCORRENCIAS INDEX (FK_HD_OCORRENCIAS_CLIENTE), HD_STATUS INDEX (PK_HD_STATUS),
>ORDEM_SERVICO_DIVISAO INDEX (PK_ORDEM_SERVICO_DIVISAO), HD_PROBLEMAS INDEX (PK_HD_PROBLEMAS))
>
>now, if I remove the clientes join... :
>
>SELECT
>HD_OCORRENCIAS.NUMERO
>FROM HD_OCORRENCIAS
>INNER JOIN HD_STATUS ON HD_OCORRENCIAS.STATUS = HD_STATUS.STAT_ID
>INNER JOIN ORDEM_SERVICO_DIVISAO ON HD_OCORRENCIAS.SISTEMA = ORDEM_SERVICO_DIVISAO.IDDIVISAO
>INNER JOIN HD_PROBLEMAS ON HD_OCORRENCIAS.PROBLEMA = HD_PROBLEMAS.PROB_ID
>WHERE (HD_STATUS.STAT_PAINEL = 2)
>
>plan used :
>PLAN JOIN (HD_STATUS INDEX (HD_STATUS_IDX1), HD_OCORRENCIAS INDEX (FK_HD_OCORRENCIAS_STAT),
>ORDEM_SERVICO_DIVISAO INDEX (PK_ORDEM_SERVICO_DIVISAO), HD_PROBLEMAS INDEX (PK_HD_PROBLEMAS))
>
>very fast, almost instantly.
>
>I havent got a clue to whats happening here. Why the optmizer is going for CLIENTES NATURAL,
>and why if I remove some of the joins is starts using the regular FK index??

Hi Rudi!

The optimizer thinks that the plan it chooses is the best one, I don't know the internals of Firebird and cannot answer why (even if I had, you didn't say which Firebird version you use and the answer could have been different with different versions). However, the two first selects you described shows how to fix your problem, simply add +0 (or ||'' if it is not a numerical field) like this:

SELECT
HD_OCORRENCIAS.NUMERO
FROM HD_OCORRENCIAS
INNER JOIN HD_STATUS ON HD_OCORRENCIAS.STATUS = HD_STATUS.STAT_ID
INNER JOIN ORDEM_SERVICO_DIVISAO ON HD_OCORRENCIAS.SISTEMA = ORDEM_SERVICO_DIVISAO.IDDIVISAO
INNER JOIN HD_PROBLEMAS ON HD_OCORRENCIAS.PROBLEMA = HD_PROBLEMAS.PROB_ID
INNER JOIN CLIENTES ON HD_OCORRENCIAS.IDCLIENTE+0 = CLIENTES.IDCLIENTE
WHERE (HD_STATUS.STAT_PAINEL = 2)

That at least ought to be a strong enough hint for the optimizer to choose a better plan.

HTH,
Set