Subject Re: [firebird-support] Optimize query for date ranges
Author setysvar
>SELECT * FROM ARBEITSEINTEILUNG AS a where a.baustelle = '12345' AND
>a.DATUM >= '05.10.2015' AND a.DATUM <= '11.10.2015';
>there are two indices on the field DATUM: One ascending, the other one descending.
>Records DATUM >= '5.10.2015' = 102
>Records DATUM <= '11.10.2015' = 26
>It seems that both queries need to be executed and then the subset of
>matching data for both date conditions is provided.

No, Firebird isn't as dumb as that. Your plan says that IDX_ARBEITSEINTEILUNG1 is the only index that is used, and I'm pretty certain that index is your ascending date index (unless the datum field has lousy selectivity and IDX_ARBEITSEINTEILUNG1 is an index for a.baustelle). I think the optimizer changes your date comparison to a.DATUM BETWEEN '05.10.2015' AND '11.10.2015'.

>The performance is not my main concern in this case - it´s more about to
>learn to improve queries.

You're likely to experience queries that needs to be improved, but I don't think the dates in your example can be improved.