Warn users when they run slow queries

[jpountz]

Historically we have done our best to make sure that queries are unlikely to get slow:

• fields are indexed by default
• fields require indexing to enable searching
• except the script query, none of our queries perform linear scans

Unfortunately, this doesn't cover all cases, and you can still run range queries on keyword fields or queries with leading wildcards on text fields, which can be super slow as well. It makes the above rules feel somewhat arbitrary. Moreover, lots of users have rarely queried fields, and they'd rather accept that the rare queries that use these fields are slower than take the indexing and space overhead.

There are plans to enable more options that trade search speed for more flexibility, lower disk usage or lighter indexing load. One issue with this move is that we might get an increasing number of users wondering about why their queries are slow. For instance it's easy to enable an option that makes search slower when small volumes of data are indexed, then forget about it, and months later have issues with 10x larger data volumes and queries that no longer meet SLAs.

One way to make it less surprising would be to push the information to the user, so that it would be obvious that something in the query is not optimal, e.g.

• it uses a script query,
• it has a prefix query on a text field and index_prefixes is not enabled,
• it uses a wildcard query with a leading wildcard,
• etc.

The way I'm thinking it could work would be by making Elasticsearch return this information in an HTTP header and make Kibana recognize it, so that it could insert a visual indication on visualizations that use slow queries.

Relates to #29050, which is about disabling slow queries entirely

[elasticmachine]

Pinging @elastic/es-search (:Search/Search)

[eskibars]

One way to make it less surprising would be to push the information to the user, so that it would be obvious that something in the query is not optimal

I'm not opposed to providing this information directly to the user / at the time the query is done, but I also worry that relying on a user (who may not have information on the mappings, etc) to self-report upwards to a cluster administrator that does have that information / can make an appropriate change isn't always likely to happen and actually may be undesirable from an administrative perspective in some cases (as an administrator, do I really want every user that experiences a slow query to be exactly aware that I am responsible and could do something about a warning they see on every slow query? Maybe not).

My preference as an administrator would most often be to have statistics on how often something is slow / how slow it is relative to alternatives, and what the costs of those alternatives would be. There are certainly cases where I'd want to be immediately aware of a slowness (e.g. in dev-tools if I were building a new query for an app I was building), but I'd want the option to disable such warnings in a UI like Kibana to avoid a squeaky wheel problem in some cases.

[javanna]

The idea of a header was brought up to signal slowness in this issue. Would the header be returned by search directly? Now that async search is available, we can better support slow queries. Users are notified when slow queries are moved to the background and they get partial aggregation results through get async search API. That is why I wonder if such a header to signal slowness would still be useful.

In the context of runtime fields, we are considering adding the indication of slowness or even the cost of querying and aggregating on a field in the field caps API response. That would allow a UI to warn users before they run the query, that accessing some fields will make the query slow. Thoughts around this?

[jpountz]

That is why I wonder if such a header to signal slowness would still be useful.

I think it's useful in the sense that we could let the user know why the query is slow, and not only that it is slow. But I don't have a preference regarding whether we should use response headers or rather add this information to field caps.

[javanna]

We have been thinking about whether we should disallow using runtime fields in certain places (for instance filtered aliases and role queries) as they may have an impact on every user query, implicitly. We initially considered signaling that a runtime field may cause slowness as part of field_caps, but we then changed our minds in the attempt of making as less distinctions as possible between runtime fields and ordinary fields. A runtime field should be treated like any other field.

We already support heavy queries that can slow things down and administrators are responsible for setting aliases and role queries up, knowing their impact, so maybe they should not receive special treatment.

The need to warn users about slow queries is still very valid, and I wonder if we should look at this from an even broader perspective, as there are multiple things that could be done in the context of supporting heavy queries.

For instance, users can already disable queries that are deemed expensive through a cluster settings (queries against runtime fields are deemed expensive too), but maybe there should be the option to disallow heavy queries for specific roles, or warning users and making them aware would be enough?

[PhaedrusTheGreek]

The current workflow for investigating slow queries might be something like:

1. Log to slow query log
2. Try the query in the query profiler to find the slow functioning component.

It would be helpful in the context of issue identification if there were some logging such as:

• indices stats / time spent in milliseconds on runtime fields, regex search, etc
• slow query log that indicates some breakdown of what took so long

Ideally it would be nice, in the case of runtime fields, to know exactly which fields take exactly how many milliseconds of search time, so that Kibana could easily suggest indexing.

[javanna]

This has been open for quite a while with no activity. While it is something that may be interesting to solve, it is a difficult problem to solve and it does not align with our current priorities. For now I'm going to close this as something we aren't planning on implementing. We can re-open it later if needed.