Fabiano Bonin wrote:

>
> Anyway, do you think the fact CTE_3 is exploded once for each row when
> i have a constant join condition is something that can be improved?

Probably. However, cross joins are not a very common thing, so it never
was a priority for the optimizer ;-)

AFAIU, your issue has nothing to do with CTE, look:

select *
from rdb$relations r
left join rdb$relation_fields rf using (rdb$relation_name)
cross join rdb$dependencies d

PLAN JOIN (JOIN (R NATURAL, RF INDEX (RDB$INDEX_4)), D NATURAL)

i.e. we have the same issue here: the independent stream D is linked as
a slave stream of a nested loop join, thus being executed many times.

Currently, the only way to "cache" the stream is to use a merge join,
but it requires a join condition to exist (and be non-constant), so it
cannot be applied to independent streams.


Dmitry