Re: [Firebird-devel] Another possible optimizer improvement?

[Arno B. <fir...@ab...>]

Hi,

> I am running into a performance problem with a query, which I think is
> the result of a quirk of the optimizer.
>
> Consider the following query:
>
> select
>   Asset.*
> from
>   Asset
> where
>   exists( select
>             TIMELINE.tln_id
>           from
>             TIMELINE
>           where
>             (TIMELINE.TLN_ASS_ID = Asset.ASS_ID) and
>             (TIMELINE.TLN_CHA_ID = :ChannelID) and
>             (TIMELINE.TLN_Logdate between :ReportStart and :ReportEnd)
>         )

Dmitry already explained why optimizer chooses the best currently is
possible.
Your query is probably slow because for every record in Asset the sub-query
is executed.
Further my question is on INDEX (TLN_CHA_ID, TLN_DATETIME).
The optimizer choose this one, but how many duplicates are there on
ChannelID ?
What happens when you also create a index on (TLN_ASS_ID, TLN_CHA_ID) ?
This problem should be solved when we get segment-selectivities as Dmitry
said.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird links :
  http://www.firebirdsql.com
  http://www.firebirdsql.info
  http://www.fingerbird.de/
  http://www.comunidade-firebird.org/


Nederlandse firebird nieuwsgroep :
news://80.126.130.81