Query optimizer in 2.5.8 and 3.x is not using importan index (but early 2.5
version uses it)
--------------------------------------------------------------------------------------------
Key: CORE-6070
URL: http://tracker.firebirdsql.org/browse/CORE-6070
Project: Firebird Core
Issue Type: Bug
Affects Versions: 3.0.4, 3.0.3, 2.5.8
Environment: Windows - Tested with SuperClassic 2.5.0, 2.5.8, and
current 3.
Reporter: Daniel
Hi;
On my database one table is this:
CREATE TABLE MOVI
(
MOVI Char(10) NOT NULL COLLATE ES_ES_AI,
PROC Char(10) NOT NULL COLLATE ES_ES_AI,
MIEM Char(10) NOT NULL COLLATE ES_ES_AI,
TIPO Char(1) NOT NULL COLLATE ES_ES_AI,
AVIS Char(3) NOT NULL COLLATE ES_ES_AI,
HECH Char(1) NOT NULL COLLATE ES_ES_AI,
FECH Char(8) NOT NULL COLLATE ES_ES_AI,
HORA Char(4) NOT NULL COLLATE ES_ES_AI,
DSCR Varchar(200) NOT NULL COLLATE ES_ES_AI,
FOJA Varchar(10) NOT NULL COLLATE ES_ES_AI,
PRES Char(1) NOT NULL COLLATE ES_ES_AI,
REPI Char(1) NOT NULL COLLATE ES_ES_AI,
OWNR Char(10) NOT NULL COLLATE ES_ES_AI,
TEXT Blob sub_type 0,
"EDIT" Char(23) NOT NULL COLLATE ES_ES_AI,
CONSTRAINT MOVI_PK PRIMARY KEY (MOVI)
);
CREATE INDEX MOVI_FECH ON MOVI (FECH,HORA);
CREATE INDEX MOVI_HECH ON MOVI (HECH);
CREATE INDEX MOVI_MIEM ON MOVI (MIEM);
CREATE INDEX MOVI_OWNR ON MOVI (OWNR);
CREATE INDEX MOVI_PRES ON MOVI (PRES);
CREATE INDEX MOVI_PROC ON MOVI (PROC);
CREATE INDEX MOVI_REPI ON MOVI (REPI);
CREATE INDEX MOVI_TIPO ON MOVI (TIPO);
The database has more than one millon records in this table and 46.000 records
in table PROC. I have to do this query:
select
"PROC"."PROC","PROC"."CARP","PROC"."ACTO","PROC"."DEMA","PROC"."OBSE","TPRO"."DSCR"
as
"D_TPRO","PROC"."OJUD","PROC"."INST","PROC"."EXP1","PROC"."EXP2","PROC"."EXP3","PROC"."EXP4"
from "PROC" left outer join "TPRO" on "PROC"."TPRO"="TPRO"."TPRO" left outer
join "OJUD" o1 on "PROC"."OJUD"=o1."OJUD" left outer join "ZONA" o2 on
o1."ZONA"=o2."ZONA" where ((("PROC"."PROC" in (select distinct "PROC" from
"MOVI" where "MOVI"."TIPO" in ('1','A','B') and ("MOVI"."FECH" between
'20190301' and '20190412') and "MOVI"."MIEM" = '15JMS45D7A' and "MOVI"."HECH" =
'N' order by "PROC")))) order by
o2."ORDE",o1."ORDE",lower("PROC"."ACTO"),lower("PROC"."DEMA")
On early 2.5.x versions, this query need up to 1 minute in the test machine to
finish, but the same machine 2.5.8 and 3.x are using more than 20 minutes.
Under 2.5.0 the query optimizer builds a plan using MOVI_FECH:
PLAN SORT ((MOVI INDEX (MOVI_PROC, MOVI_FECH)))
But under 2.5.8 the query optimizer does not use MOVI_FECH
PLAN SORT ((MOVI INDEX (MOVI_PROC)))
I found that 2.5.8 only uses MOVI_FECH if I do ("MOVI"."FECH" = '20190301') but
it does not use that index if I use between or if I use >= and/or <=.
I thing this is the problem that makes my query to need 20 minutes instead of 1
minute.
Thank you.
--
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
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
