ESQL: LOOKUP JOIN push down optimizations

[alex-spies]

Compared to ENRICH (and other, similar plans like GROK, DISSECT and EVAL), we do not push down LOOKUP JOINs.

That leads to suboptimal plans, e.g. because projections before and after the LOOKUP JOIN cannot be combined.

Example:

              FROM test
            | LOOKUP JOIN lookup_index1 ON field
            | RENAME foo AS b
            | LOOKUP JOIN lookup_index2 ON field
            | DROP b*

The RENAME foo AS b in between the LOOKUP JOINs becomes an EsqlProject, which cannot be combined with the EsqlProject that comes from the DROP b* because the LOOKUP JOIN lookup_index_2 is in the way. As a result, we cannot determine that the field foo obtained from the lookup_index1 is actually dropped and unused in the end.

Edit: Let's also double check that there are no other existing optimizer rules that could (somewhat) easily be applied to LOOKUP JOIN.

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)

[alex-spies]

Before closing this issue, let's also double check that there are no other existing optimizer rules that could (somewhat) easily be applied to LOOKUP JOIN. (If this ends up expanding the scope too much, we can track this in a separate issue once we find out more.)

[alex-spies]

For pushing down LOOKUP JOIN past Projects, I have a draft open.

Pushing down past SORTs is #121884.

Out of the other existing optimizer rules, I think PropagateEvalFoldables could be interesting to expand to optimize for cases where we lookup on a constant, as in ... | EVAL key = 1 | LOOKUP JOIN lu_idx ON key, but this requires the infrastructure for LOOKUP JOIN to leverage this situation.

I don't see other optimizer rules that look like they should be carried over to LOOKUP JOIN.