Hello,

Finally, I think that it uses three indices because you specify
"select count(*)" and not "select count(br)".

count(*) includes null values but the where clause is restricted to
known values so count(br) is sufficient.

Try it instead of count(*) and maybe it will use RDB$FOREIGN231.

Regards

Alexandre

--- In firebird-support@yahoogroups.com, Milan Babuskov <milanb@...>
wrote:

>
> Hi,
>
> Firebird 1.5.3 Classic on Linux
>
> I got this simple STARTING WITH query:
>
> SQL> set planonly;
> SQL> select count(*) from kbst where br starting with 'K2';
>
> PLAN (KBST INDEX (RDB$FOREIGN231,RDB$PRIMARY39,IDX_KBST1))
>
> SQL> show index kbst;
> IDX_KBST1 INDEX ON KBST(BR, SR)
> RDB$FOREIGN231 INDEX ON KBST(BR)
> RDB$FOREIGN232 INDEX ON KBST(SR)
> RDB$PRIMARY39 UNIQUE INDEX ON KBST(BR, ID)
>
> Is Firebird really going to read all three indexes?
>
> I'd remove some, but of those 4 indexes, 3 are used by PK/FK
> constraints. IDX_KBST1 is the only candidate, but I have a lot of:
>
> WHERE BR = ? and SR = ?
>
> queries in many places in application (and selectivity for IDX_KBST1 is
> much better than RDB$FOREIGN231 or 232).
>
>
> Thanks,
> --
> Milan Babuskov
> http://www.flamerobin.org
>