Hi Kjell,

I understand your frustration, but I think it is unrealistic to
expect any database to know and decide stuff about the data the best
way every time.

You have quoted a figure of up to 50% (which it may feel like but I
doubt it). I think you just need to take a reasonable perspective on
it. The optimiser has to deal with things that neither of us probably
even consider.

There are times it will make dumb decisions, but you can normally
massage your query around a bit or if all else fails tell it your own
plan. As I understand, FB 2 has some large improvements in the
optimiser. I also find that in many occasions, a join can be used
instead of a subquery, and this often gives a better performance (but
it is not always possible).

Hope that helps.

Adam



--- In firebird-support@yahoogroups.com, Kjell Rilbe
<kjell.rilbe@a...> wrote:

> Aage Johansen wrote:
>
> > Kjell Rilbe wrote:
> > > As I understand it, the problem is that it will repeat the

subquery for

> > > each record in the outer query and that the low index

selectivity of the

> > > subquery indices causes these subqueries to execute rather

sluggishly.

> >
> > Maybe not quite. I think Fb is waisting time on indexes which

add less

> > than nothing to performance.
>
> Couldn't Firebird be made to detect this problem automatically? Or
> perhaps the way it evaluates index statistics could be adjusted to
> reduce that the risk of this occurring. It does happen in too many
> situations as it is now. It wouldn't be a big problem if it rarely
> happened, but now I seem to run into it on some 10-50% of all

queries I

> create. I don't know about you, but I personally don't think that's

good

> enough.
>
> Kjell
> --
> --------------------------------------
> Kjell Rilbe
> Adressmarknaden AM AB
> E-post: kjell.rilbe@a...
> Telefon: 08-761 06 55
> Mobil: 0733-44 24 64