Bad Optimzer PLAN depending on joined tables in the query
---------------------------------------------------------
Key: CORE-3432
URL: http://tracker.firebirdsql.org/browse/CORE-3432
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 2.1.4
Environment: Win XP OR WinNT 2003 (tested on both), plain firebird
classic install, ibexpert to run queries
Reporter: rudi feijo
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))
executed 7.000ms (full table scan on clientes)
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
--INNER JOIN CLIENTES ON HD_OCORRENCIAS.IDCLIENTE = CLIENTES.IDCLIENTE
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.
now...ill keep the clientes table on the query. and I will remove
ORDEM_SERVICO_DIVISAO and HD_PROBLEMAS :
query :
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 (HD_STATUS INDEX (HD_STATUS_IDX1), HD_OCORRENCIAS INDEX
(FK_HD_OCORRENCIAS_STAT), CLIENTES INDEX (PK_CLIENTES))
very fast again.
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?
PS : Of course I need to pull data from the joined tables, I excluded those
from the select statement to make it brief.
If I need to post more information like the table DDL's please let me know. I
havent done it because they are rather huge.
Thanks for your assistance
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Xperia(TM) PLAY
It's a major breakthrough. An authentic gaming
smartphone on the nation's most reliable network.
And it wants your games.
http://p.sf.net/sfu/verizon-sfdev
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
