On Tue, Oct 13, 2015 at 3:47 PM, 'Parzival' parzival1969@... [firebird-support] <firebird-support@yahoogroups.com> wrote:

Hello Thomas,

the result set contains 3 record for criteria.

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.

Here's the query:

SELECT * FROM ARBEITSEINTEILUNG AS a where a.baustelle = '12345' AND a.DATUM >= '05.10.2015' AND a.DATUM <= '11.10.2015'; 

I don't think that's the way it works.  The query plan shows a one key  lookup, because that's the main stream of the query execution.  However, Firebird can use multiple indexes on a single query, which it will do this time.  First it builds a bit map of the db_keys of records with baustelle = 12345.   It then uses one of the indexes (ascending one would hope) on DATUM to get the db_keys of records with DATUM between 05.10.2015 and 11.10.2015 (which should be stored as a date type and not a string).  Firebird then ANDs the two bitmaps and returns the rows that match both criteria.  

You might get better performance on this query with a compound index on baustelle and datum.  You'll pay for it when you insert, update, or delete rows from arbeitseinteilung.

You seemed concerned about the number of reads this query requires.  Did you measure the number of reads with a cold cache (i.e a freshly started database) or one that had already done something with arbeitseinteilung?  46 is a low number for a completely cold cache.  

Good luck,

Ann