| Subject | Re: natural in plan |
|---|---|
| Author | Svein Erling |
| Post date | 2003-08-27T08:53:15Z |
Well, Mariano, I cannot explain, but I can guess what is going on (note that I am guessing, so don't take what I say as fact):
In your first query, the optimizer calculates that it is better to use an index for HIST_EVENTO covering both ID_CUENTA and FECHA_HORA and leave CUENTA to NATURAL, than to use an index for FECHA_HORA for HIST_EVENTO and an index ID_CUENTA for CUENTA.
Logically, your second query ought to be the same, but in theory there is nothing preventing you from having a join to CUENTA that references both HIST_EVENTO and ZONA (since they are joined before CUENTA). Since ZONA is left joined, HIST_EVENTO has to come before ZONA in the plan, and if CUENTA is joined to ZONA (which we know it isn't, but it could have been), CUENTA also have to come after HIST_EVENTO. Hence, your second query is preventing the optimizer from using the plan it would consider most appropriate.
One way to make your first query perform better, would be to simply remove ID_CUENTA from the index IDX_HIST_EVENTO_FC. That should make the optimizer understand that it ought to use an index for CUENTA. Another thing worth trying, is to change to
select *
from hist_evento he
join cuenta c on c.id_cuenta = he.id_cuenta
left join zona z on z.id_zona = he.id_zona
where (he.fecha_hora >= '2003-08-01' or 2=0)
to prevent using the index for fecha_hora at all (I think Arno told me at the conference in Fulda, that descending indexes were only effective with MAX, and ascending indexes are not useful with >=. That was quite a few months back, so maybe this has been improved in later releases).
Basically, I think that Firebird chose a bad plan for you (I don't know whether this is due to a weakness in Firebird or bad creation of indexes on your part), and that you by accident discovered another Firebird limitation (that it does not check whether the join to zona actually has any reference to cuenta before determining the order of the join) that helped you get the plan you wanted. Thanks for teaching me how to limit which indexes Firebird can use in JOINs!
Though don't forget, I am just guessing here...
Set
-leaving it up to Helen and Arno to correct my assumptions.
In your first query, the optimizer calculates that it is better to use an index for HIST_EVENTO covering both ID_CUENTA and FECHA_HORA and leave CUENTA to NATURAL, than to use an index for FECHA_HORA for HIST_EVENTO and an index ID_CUENTA for CUENTA.
Logically, your second query ought to be the same, but in theory there is nothing preventing you from having a join to CUENTA that references both HIST_EVENTO and ZONA (since they are joined before CUENTA). Since ZONA is left joined, HIST_EVENTO has to come before ZONA in the plan, and if CUENTA is joined to ZONA (which we know it isn't, but it could have been), CUENTA also have to come after HIST_EVENTO. Hence, your second query is preventing the optimizer from using the plan it would consider most appropriate.
One way to make your first query perform better, would be to simply remove ID_CUENTA from the index IDX_HIST_EVENTO_FC. That should make the optimizer understand that it ought to use an index for CUENTA. Another thing worth trying, is to change to
select *
from hist_evento he
join cuenta c on c.id_cuenta = he.id_cuenta
left join zona z on z.id_zona = he.id_zona
where (he.fecha_hora >= '2003-08-01' or 2=0)
to prevent using the index for fecha_hora at all (I think Arno told me at the conference in Fulda, that descending indexes were only effective with MAX, and ascending indexes are not useful with >=. That was quite a few months back, so maybe this has been improved in later releases).
Basically, I think that Firebird chose a bad plan for you (I don't know whether this is due to a weakness in Firebird or bad creation of indexes on your part), and that you by accident discovered another Firebird limitation (that it does not check whether the join to zona actually has any reference to cuenta before determining the order of the join) that helped you get the plan you wanted. Thanks for teaching me how to limit which indexes Firebird can use in JOINs!
Though don't forget, I am just guessing here...
Set
-leaving it up to Helen and Arno to correct my assumptions.
--- In firebird-support@yahoogroups.com, PODESTA Mariano APRE <SIDTMPO@S...> wrote:
> hi, i have a problem with a select.
> fb1.5 superserver rc5 on win2k/winnt
>
> with the 'left join' at last i get this plan:
>
> select *
> from hist_evento he
> join cuenta c on c.id_cuenta = he.id_cuenta
> left join zona z on z.id_zona = he.id_zona
> where he.fecha_hora >= '2003-08-01'
>
> PLAN JOIN (JOIN (C NATURAL,HE INDEX (IDX_HIST_EVENTO_FC)),Z INDEX (PK_ZONA))
>
> indexes:
> IDX_HIST_EVENTO_FC = ID_CUENTA, FECHA_HORA
> PK_ZONA = ID_ZONA
>
> but, with the 'left join' next to 'from', i get this:
>
> select *
> from hist_evento he
> left join zona z on z.id_zona = he.id_zona
> join cuenta c on c.id_cuenta = he.id_cuenta
> where he.fecha_hora >= '2003-08-01'
>
> PLAN JOIN (JOIN (HE INDEX (IDX_HIST_EVENTO_FH),Z INDEX (PK_ZONA)),C INDEX
> (PK_CUENTA))
>
> indexes:
> IDX_HIST_EVENTO_FH = FECHA_HORA
> PK_ZONA = ID_ZONA
> PK_CUENTA = ID_CUENTA
>
> i have this situation on many querys an it gets too slow.
> the second way runs perfect but i don't know why.
> can anyone explain me this?
>
> thanks,
> mariano