Elasticsearch fast query but slow response time when retrieving _source even if nested fields are in _source_exclude

Yep, you're experiments are spot on. You're correct with regards to including the _source slowing down the query, and that larger documents tend to slow down the query more than smaller documents.

I believe -- although this could be wrong, checking to see if it's true -- that nested docs require additional file seeks which would explain the slowdown with documents that have more nested than others.

Regardless, _source_exclude is not going to help speed because the main slowdown is the file seek and loading from disk. This happens regardless of source filtering, which can only proceed to filter the source after it has already been loaded. It may even slow things down further, as the fetch phase has to A) load the entire source then B) parse and exclude a portion of the source before returning.

I don't think there is anything that can be optimized here other than returning fewer hits at a time, or moving your nested docs over to a parent/child scheme. That should give you the same relational data, but puts the nested values into independent child documents which won't affect the retrieval of the parent.

1 Like