|Subject||Re: [firebird-support] Query that was very fast under the oldest 2.5 versions are very slow under 2.5.8 or 2.5.9|
I think there is not a problem in my query, but there is a problem in the query optimiezar;
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)))
But, if with 2.5.8 I only execute the MOVI filter:
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"
... then I get a super-fast answer and the optimizer uses MOVI_FECH
PLAN SORT ((MOVI INDEX (MOVI_FECH, MOVI_MIEM)))
This is not a little diference. If the engine have to analize 1.1 millon records one by one, and more over when MOVI is a very big table with blobs (I don't know if the engine is fetching blobs for this case but if it does ... you know).
So the IN is not a problem. The problem is about why the enigine is not using MOVI_FECH index when it is there and is very usefull for the case.