Thanks to both for the explanation. Rgds Diego
--- In [email protected], Ann Harrison <aharrison@...> wrote: > > 2011/9/13 Svein Erling Tysvær <svein.erling.tysvaer@...> > > > > > > > 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) > > ... > > 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. > > > > In case there's someone out there wondering what histograms are in this > context and what they have to do with optimization, let me elaborate a bit. > Firebird keeps the selectivity of indexes and in the case of compound > indexes, the selectivity of each field in the key. The selectivity is the > number of distinct values compared to the total number of entries in the > index. So, if you've got a table with 100,000 records and one of the > fields has five distinct values, an index on that field would have a > terrible selectivity. On the other hand, if 999,900 records had one value > for the field and the other four values were distributed among the remaining > 100 records, then for those four values, the index would have good > selectivity. A histogram records the distribution of values in an index, > so it's a much more sensitive way of determining the usefulness of an index, > providing you know the values you're looking for. > > Different databases optimize queries at different times. Firebird optimizes > its queries when they are presented so for joins and prepared queries with > parameters, it doesn't know all the input values. Some other systems delay > optimization until all parameters are known, so the inner part of a join is > optimized for each new value from the outer part of the join and a > parameterized query is optimized for each new value of the parameters. > Systems like that can make great use of histograms, but at a significant > cost for re-optimization. > > If I understood the query presented, it was looking for records in a > specific range of dates. Range queries are particularly awkward to optimize > based on selectivity. And the dates were explicit. So for that particular > query, histograms would be a great help. > > > Good luck, > > Ann > > > [Non-text portions of this message have been removed] >
