HS 6 result fetch time is too high

Hibernate Search
1

In prod environment there are around 4000 record for a day—> it took around 1.5 min to respond
In Non prod there is around 700 records for a day -----> it took only 15 sec to respond

I am using date range query in HS6 + elastic
I have put RESULT_SET_THRESH_HOLD =500
image

Please let me know the root cause of this. Is it really because of large number of data present?

Here is the range predicate

image
image947×364 10.8 KB

2

I’d say it’s more likely that the query itself was fast, but loading the entities was slow. Especially considering you’re loading an unusually large number of entities. To be sure, enable logging of Elasticsearch requests by setting the log level to TRACE for category org.hibernate.search.elasticsearch.request. (Don’t do that in production though, it will slow down your application and fill your logs very fast)

You will see a log entry for every request sent to Elasticsearch, along with the execution time.

If that execution time is high, it means the problem is executing your query in Elasticsearch:

  • Check that you’re not executing queries that are know to be slow. In particular, a wildcard query with a leading wildcard will always be slow, whatever you do.
  • Consider scaling up your Elasticsearch cluster, or allocating more resources to your Elasticsearch nodes.

If that execution time is low, it means the problem is likely loading your entities from the database. Then the problem boils down to optimizing entity reads. Ideally you should inspect database logs to see what is slow exactly, but here are some solutions you can try out:

  • If you’re using @DocumentId on a property that is not the entity ID, make sure to add database indexes on the corresponding column(s).
  • Try setting a higher loading fetch size. Note this may improve or decrease performance; you just have to try.
  • Try making more associations lazy in your entities, either directly in your mapping (remove any suspicious fetch = EAGER) or by setting a fetch graph in your query
  • Try enabling batch fetching either globally or for specific associations.
  • If you have a second-level cache and are often retrieving the same entities, try enabling second-level cache lookups. That will only help if you’re often retrieving the same entities, though.