Subject Re: [firebird-support] Some questions about indices
Author Dmitry Yemanov
"fabiano_bonin" <fabiano@...> wrote:
> When indices statistics are updated?

1) when an index is created
2) when an inactive index is activated
3) when SET STATISTICS is issued

> Plan
> Why the optimizer uses the index with lower selectivity in the case
> above, if in the where clause i'm searching the primary key?

IDX_TEST is used for sorting. In this case (when ORDER plan is used) all
IB/FB versions prior to FB 2.0 don't report you indices (if any) used to
filter the result set, although they are in fact used. With FB 2.0, you'll


which explains the internal activity much better.

> I'm having performance problems with tables with milion records, when
> i search in a column which has an index with high selectivity but the
> sort is made in a column which has an index with low selectivity.

An index PK_TEST is scanned to evaluate your WHERE clause. The result of
this scan is stored in the intermediate sparse bitmap. Then the engine
performs a navigational scan on IDX_TEST, trying every record number against
the bitmap. So you get a full index scan for IDX_TEST, even if the bitmap
contains a single value. This is a reason of your performance issues.

As a workaround, I'd suggest to order by dummy expression (<col> + 0), this
will disable any possible index navigation and force a SORT plan instead. It
should be much better for you, as the filtering is performed before the
external sorting.