elastic / elastic/elasticsearch
Mv_expand and projections and filters
- 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
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