Actually, a tool I've found quite useful for tuning/optimizing is IBConsole
(I know, boo hiss), though I know IBExpert will also do what I use IBConsole
for.
Anyways, have it print out a statistical summary of your database. Check
the index definitions to make sure you have a somewhat even distribution in
your buckets... Or at least, don't have all of your data in one bucket.
That's a bad thing. An example of an index that would have a bad
distribution would be on an integer/varchar field which only holds a true or
a false value. My rule of thumb is to not use indexes with bad
distributions, because IB/FB will be tempted to use it. It doesn't seem
like there's any logic built into the engine to not use non-selective
indexes (And if there isn't, call it a feature request. :)), so I just don't
use them (Or delete them if I find them because something I had planned
hadn't panned out). Usually, deleting indexes doesn't seem like it'd make a
difference, but if it's a bad, non-selective index, it can make a huge
difference for the better.
Another thing is to check the plan that is being chosen by the engine. Copy
the query into IBConsole, IBExpert, IBPickYourOwnUtility, etc, and check the
plan. Any time you see a natural as an index, that's a very bad thing.
Also, if you see an index that's being used that has a bad distribution that
you found by looking at the statistics as outlined above, that's also a bad
thing. Again, that's why I just delete the bad ones. :)
That's my learned experience with Firebird. Tuning queries is often-times a
database specific task.. Though, I have found FB to be much pickier on
queries and indexes than SQLServer (My guess is SQLServer has some code
built in for ignoring non-selective indexes, and probably quite a few other
things to make things smoother for the developer). On the other hand, FB,
because it is picky, generally (Though not always) leads to writing better
queries. I'm sure others have more refined techniques, but I haven't needed
to get more refined with it, so my rough techniques work for me. :)

- Jason