| Subject | Re: Upgrade to FB2.01 and performance drops |
|---|---|
| Author | Adam |
| Post date | 2007-05-20T23:42:11Z |
--- In firebird-support@yahoogroups.com, "trtoms" <trtoms@...> wrote:
automatically converts queries from IN to EXISTS where possible. I get
the same plan in both occasions.
One difference between 1.5 and 2.x is the way it no longer converts
NOT IN to NOT EXISTS because doing so causes wrong data in some cases.
As I said in my prior post, check the release notes if you want to see
the details.
My bet is that your problem comes down to something simple.
The 2.x optimiser is able to consider more paths than the 1.5.x
optimiser. It can generate plans that the 1.5.x optimiser would not
have even considered. This is usually a good thing. It means that
queries that used to be slow may be able to run faster using the
better plan. Occasionally though (perhaps due to bad statistics, or
perhaps due to a bug or limitation in the optimiser), it either gives
more credit to a bad plan than it is worth, or less credit to a good
plan than it should have. This can lead to the wrong plan being selected.
The good news is that it is reasonably easy to trick the optimiser
into thinking a specific index is irrelevant, so if you identify the
problematic query and the plan it takes, a couple of minutes work can
make it at worst use the same plan as it used to.
Adam
>True, but the optimiser in 1.5 (and therefore presumably 2.x)
> --- In firebird-support@yahoogroups.com, Fulvio Senore <mailing@>
> wrote:
> >
> >
> <snip> A few days ago I posted a message
> > (http://tech.groups.yahoo.com/group/firebird-support/message/86039)
> > about a query using 'IN' and a subquery: it was deadly slow.
> > I am using V2.0.1 and I don't know how it could work with V1.5, but
> if
> > you want I can send you a sample database (less than 1 MB) that shows
> > the problem.
> > Just let me know.
> >
> > Fulvio Senore
> >
>
> We stopped using IN predicate some time ago wherever possible, as the
> general case seemed to be better using EXISTS instead. Of course,
> there's no "one size fits all" option for every query, but EXISTS will
> back out of the test when the first satisfying result is detected, and
> this can often save time.
automatically converts queries from IN to EXISTS where possible. I get
the same plan in both occasions.
One difference between 1.5 and 2.x is the way it no longer converts
NOT IN to NOT EXISTS because doing so causes wrong data in some cases.
As I said in my prior post, check the release notes if you want to see
the details.
My bet is that your problem comes down to something simple.
The 2.x optimiser is able to consider more paths than the 1.5.x
optimiser. It can generate plans that the 1.5.x optimiser would not
have even considered. This is usually a good thing. It means that
queries that used to be slow may be able to run faster using the
better plan. Occasionally though (perhaps due to bad statistics, or
perhaps due to a bug or limitation in the optimiser), it either gives
more credit to a bad plan than it is worth, or less credit to a good
plan than it should have. This can lead to the wrong plan being selected.
The good news is that it is reasonably easy to trick the optimiser
into thinking a specific index is irrelevant, so if you identify the
problematic query and the plan it takes, a couple of minutes work can
make it at worst use the same plan as it used to.
Adam