>Hi > >I'm trying yo fully understand how FB takes decisions to optimize a query in >my DB. >Please check this two queries. The results are the same, but the second query >is much better that the >first one. >It seems that the left outer join of the secondary table able FB to use their >indexes. I could not >understand why the first query reads 300k records. >If I do a query to select all the records between these days I receive 48k >records. ¿Why FB reads more >than 300k records to do this Query? > >Kind regards >Diego > >Query #1 > >Select * >From maestro_turnos mt >Where mt.fecha_turno >= '2011-01-01' >And mt.fecha_turno <= '2011-01-31' >And mt.paciente is null > >Number of records readed: 304,222 >Number of records fetched: 5,128 >Plan analisis: PLAN (MT INDEX (FK_MAESTRO_TURNOS_3)) >FK_MAESTRO_TURNOS_3: indexed on field "paciente"
Firebird doesn't have histograms yet, so it knows nothing about which field values are common and which are rare when it chooses between using the index of a probably reasonably selective field (I assume you have lots of records in maestro_turnos and that 300K is only a small fraction) where you test for - well, almost equality (although NULL is a state, not a value) or an index for a (presumably) less selective field where you test for a range. In your case, Firebird makes the wrong guess and chooses to use a suboptimal index. You may prevent Firebird from using the index, e.g. by changing to: Select * >From maestro_turnos mt Where mt.fecha_turno >= '2011-01-01' And mt.fecha_turno <= '2011-01-31' And mt.paciente+0 is null In the future, I think Firebird will implement histograms (maybe it is already part of Firebird 3?), and then I guess it will make the right decision in cases like yours. HTH, Set
