> On Apr 8, 2015, at 10:05 AM, andrew_s_vaz@... [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