| Subject | RE: [firebird-support] Re: Plan shows 3 indexes used for a simple STARTING WITH clause |
|---|---|
| Author | Svein Erling Tysvær |
| Post date | 2007-11-09T08:00:48Z |
Is the plan the same if you change the primary key to:
RDB$PRIMARY39 UNIQUE INDEX ON KBST(ID, BR)
I just remember that Firebird used to have problems choosing when there were more than one index with the same selectivity (although I believed it to mainly be a problem with 1.0 and before and not 1.5). Try and see if it helps.
Of course, if your problem is limited to 'where br starting', then you can just change to IDX_KBST1 INDEX ON KBST(SR, BR), but if you also have queries like 'where sr starting', then you just move the problem to this place.
Another way to prevent an index from being used (that I've never tried, and will not recommend in general) unless explicitly wanted, is to add a dummy field and populate it with a fixed value. Then, change the index to
IDX_KBST1 INDEX ON KBST(DummyField, BR, SR)
and include 'WHERE DummyField = 1 and BR = :Param1 and SR = :Param2' when you want the index to be used. The reason that I don't recommend this in general, is that it feels like trying to prevent the optimizer from using Arnos intelligence, and in general I find the optimizer quite clever. Admittedly, you know your data better than the optimizer and should be able to better understand which index is beneficial to use in all cases, but although I love sql puzzles, I'm very glad that I can trust the optimizer in most cases, and don't have to constantly fight it.
Set
RDB$PRIMARY39 UNIQUE INDEX ON KBST(ID, BR)
I just remember that Firebird used to have problems choosing when there were more than one index with the same selectivity (although I believed it to mainly be a problem with 1.0 and before and not 1.5). Try and see if it helps.
Of course, if your problem is limited to 'where br starting', then you can just change to IDX_KBST1 INDEX ON KBST(SR, BR), but if you also have queries like 'where sr starting', then you just move the problem to this place.
Another way to prevent an index from being used (that I've never tried, and will not recommend in general) unless explicitly wanted, is to add a dummy field and populate it with a fixed value. Then, change the index to
IDX_KBST1 INDEX ON KBST(DummyField, BR, SR)
and include 'WHERE DummyField = 1 and BR = :Param1 and SR = :Param2' when you want the index to be used. The reason that I don't recommend this in general, is that it feels like trying to prevent the optimizer from using Arnos intelligence, and in general I find the optimizer quite clever. Admittedly, you know your data better than the optimizer and should be able to better understand which index is beneficial to use in all cases, but although I love sql puzzles, I'm very glad that I can trust the optimizer in most cases, and don't have to constantly fight it.
Set
--- In firebird-support@yahoogroups.com, Milan Babuskov 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