On Apr 8, 2015, at 10:05 AM, andrew_s_...@yahoo.com [firebird-support]
firebird-support@yahoogroups.com wrote:
Let's see when histograms come to the party, it'll make things more
interesting for sure. G
Histograms may be less useful in Firebird than you might think, unless there's
a lot of work done in query preparation. In order to use the fact that there
are lots more 0's than 1's in a two valued index effectively, Firebird has to
know whether you're looking for a 1 (good) or as 0 (forget the index).
Firebird queries are optimized when they are prepared, so without major
changes, the histogram is useful only for literal values - not parameters.
Probably a query could be partially optimized at prepare time and have a final
optimization when the query is executed and all paramters are known. However,
there's also the case where values become known ony during the execution of a
join. Should Firebird do a row-by-row optimization of a nested loop join?
I wonder if you tripped over an improvement in Firebird. In 1.5, Firebird kept
only the total selectivity for compound indexes. More recent versions keep the
selectivity at each column - e.g. if you have a two valued column as the first
part of an index and a very selective colum next, Firebird 1.5 considered the
index a good candidate, even if you only matched the first part. In later,
smarter versions, it recognizes that the first part alone is not very good.
Good luck,
Ann
