Subject Re: Query optimizer is not using the useful indexes.
Author
Post date 2019-05-31T11:18:32Z
Hello;

select “PROC” from “PROC” where “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")


After testing, I finally found out what is the problem with this query. Before Firebird 2.5.4, the query optimizer always accepted to use the compound index MOVI_FECH (FECH,HORA) for optimizing the MOVI part. But since Firebird 2.5.4 and later versions of Firebird, the eninge does not use this index anymore. That (and ONLY THAT) is making my query to need 24 minutes insted of 1 minute.

I reported the case and my foundings: http://tracker.firebirdsql.org/browse/CORE-6070

Thank you.