Hi Pavel,

> > Pavel, I think you're wrong this time. The left outer join relates to
> > noti_reglas (ng), which correctly uses an index. The where-clause, however,
> > limits the left side of the left join (with all the other tables) and for
> > some reason Firebird thinks its best bet is to use natural on
> > the noti_servicio (ns) table. Though the plan doesn't quite make sense.
> > It doesn't mention the cuenta (cu) or notificacion (nt) tables, which
> > should contain vital information as to why Firebird chose the plan it did.
>
> Sorry, but I'm not wrong here :-). There is always more than one way how
> to join tables, and optimizer looks at (almost) all permutations. Note
> that LEFT points to NS, not NG which is RIGHT side of join. Becuse OUTER
> join without where condition usually make a big intermediate product, is
> pushed up to the root of the join chain and to the left (join is usually
> a loop over left side with lookup for relevant row from right side, where
> bigger set is always on left).

SELECT
cu.id_cuenta
FROM
cuenta cu
JOIN notificacion nt ON (nt.id_cuenta = cu.id_cuenta)
JOIN noti_recipiente nr ON (nr.id_notificacion = nt.id_notificacion)
JOIN recipiente re ON (re.id_recipiente = nr.id_recipiente)
JOIN noti_servicio ns ON (ns.id_noti_servicio = re.id_noti_servicio)
LEFT JOIN noti_reglas ng ON (ng.id_noti_reglas = nt.id_noti_reglas and ng.activo = 1)
WHERE
cu.id_cuenta = 696 and
nt.id_evento = 123

Orginal PLAN :

PLAN JOIN (JOIN (NS NATURAL,RE INDEX (RDB$FOREIGN56),
NR INDEX (RDB$FOREIGN57),
NT INDEX (RDB$PRIMARY48,RDB$FOREIGN50),
CU INDEX (RDB$PRIMARY1)),NG INDEX (PK_NOTI_REGLAS))

If indices are available for the list below :

cuenta cu => use index on (cu.id_cuenta)
notificacion nt => use index on (nt.id_cuenta)
noti_recipiente nr => use index on (nr.id_notificacion)
recipiente re => use index on (re.id_recipiente)
noti_servicio ns => use index on (ns.id_noti_servicio)
noti_reglas ng => use index on (ng.id_noti_reglas)

Then this PLAN should theoraticly be possible :

PLAN JOIN (JOIN (CU INDEX (IDX_CU_CUENTA),
NT INDEX (IDX_NT_CUENTA), NR INDEX (IDX_NR_NOTIFICATION),
RE INDEX (IDX_RE_RECIPIENTE), NS INDEX (IDX_NS_SERVICIO),
NG INDEX (IDX_NG_ID_NOTI_REGLAS))

But the optimizer doesn't choose between an all-possible-join-orders performance. Also this wouldn't
mean that this PLAN is quicker as the orginal PLAN.

Regards,
Arno