elastic / elastic/elasticsearch

Mv_expand and projections and filters

Open
#136,232 6 comments 1 reaction 0 assignees View on GitHub
:Analytics/ES|QL >bug backport-needed Team:Analytics
Dominant language
Java
Stars
77.9k
Forks
26.1k
PR merge metrics
PR metrics pending

Description

A user reported an issue where an ES|QL query takes several minutes to execute. I don't have the query, but I do have a task that includes a list of operators. I suspect the issue is related to `mv_expand` combined with projections.

```
from test | MV_EXPAND count_d | where count_d > 20
```

produces

```
ExchangeSinkExec[[color{f}#34, count{f}#35, count_d{r}#36, data{f}#37, data_d{f}#38, tag{f}#39, time{f}#40],false]
\_ProjectExec[[color{f}#34, count{f}#35, count_d{r}#36, data{f}#37, data_d{f}#38, tag{f}#39, time{f}#40]]
\_FieldExtractExec[color{f}#34, count{f}#35, data{f}#37, data_d{f}#38, ..]<[],[]>
\_LimitExec[1000[INTEGER],28]
\_FilterExec[count_d{r}#36 > 20[INTEGER]]
\_MvExpandExec[count_d{f}#41,count_d{r}#36]
\_FieldExtractExec[count_d{f}#41]<[],[]>
\_EsQueryExec[test], indexMode[standard], [_doc{f}#42], limit[], sort[] estimatedRowSize[144] queryBuilderAndTags [[QueryBuilderAndTags{queryBuilder=[null], tags=[]}]]
```

```
from test | KEEP * | MV_EXPAND count_d | where count_d > 20
```

produces

```
ExchangeSinkExec[[color{f}#14, count{f}#15, count_d{r}#16, data{f}#17, data_d{f}#18, tag{f}#19, time{f}#20],false]
\_ProjectExec[[color{f}#14, count{f}#15, count_d{r}#16, data{f}#17, data_d{f}#18, tag{f}#19, time{f}#20]]
\_LimitExec[1000[INTEGER],140]
\_FilterExec[count_d{r}#16 > 20[INTEGER]]
\_MvExpandExec[count_d{f}#21,count_d{r}#16]
\_ProjectExec[[color{f}#14, count{f}#15, count_d{f}#21, data{f}#17, data_d{f}#18, tag{f}#19, time{f}#20]]
\_FieldExtractExec[color{f}#14, count{f}#15, count_d{f}#21, data{f}#17, ..]<[],[]>
\_EsQueryExec[test], indexMode[standard], [_doc{f}#22], limit[], sort[] estimatedRowSize[144] queryBuilderAndTags [[QueryBuilderAndTags{queryBuilder=[null], tags=[]}]]
```

With `KEEP *` before `mv_expand`, all fields are eagerly loaded before the filter. I believe both queries should produce the same physical plan that loading fields after the filter.

I think we could push the filter (without single-value-match) to Lucene in this scenario, but that's a separate issue.

Contributor guide

Open the contributing guide

Research direction

Reproduce the two ES|QL queries using MV_EXPAND, KEEP *, and the filter, then compare their physical plans. Start with the ES|QL planning paths for projections, filters, and MV_EXPAND; done means the plans avoid eagerly loading unrelated fields and behave consistently in both query forms.

Written by the indexing model from the issue text.

Assessment

Tech stack
java
Domain
search
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.