# Query response time not scaling well with large resultsets **URL:** \ **Category:** Elasticsearch\ **Created:** [November 14, 2012, 10:23pm UTC](https://discuss.elastic.co/t/query-response-time-not-scaling-well-with-large-resultsets/9721 "2012-11-14T22:23:05Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![lifo](https://avatars.discourse-cdn.com/v4/letter/l/c77e96/32.png) [@lifo](https://discuss.elastic.co/u/lifo)\ **Post date:** [November 14, 2012, 10:23pm UTC](https://discuss.elastic.co/t/query-response-time-not-scaling-well-with-large-resultsets/9721/1 "2012-11-14T22:23:05Z")
We have a simple setup with 1 node, 1 index and 5 shards containing totally about 110 million documents. Total index size is about 30Gb. All documents are simply indexed by one field named ES\_VALUE and stores a value named ES\_SUMMARY, in addition we have some other id fields indexed. We are indexing several fields from our entities but they are all put into ES\_VALUE when indexing, i.e. there are a lot more different values for ES\_VALUE than there are documents in the index. A simple query for documents only using a wildcard query on ES\_VALUE performs very different depending on how many documents its targeting. I.e. when the result has a smaller total hit count it returns in a few mills but when result has a large total hit count it takes about 10-15sec to return. Size is set to 15 so there are only 15 documents actually returned to the client. Any suggestions on how to get the queris to perform better with large resultsets? -- ---
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [November 15, 2012, 12:19am UTC](https://discuss.elastic.co/t/query-response-time-not-scaling-well-with-large-resultsets/9721/2 "2012-11-15T00:19:06Z")
Wildcard queries are generally slow, do not expect too much. Are you on a "cold" index? If you repeat the query, you will likely see faster turnaround times. A side note, with upcoming ES 0.21 and Lucene 4 with the new AutomatonQuery, the chance is high that wildcard queries can be expected to perform faster. Jörg On Wednesday, November 14, 2012 11:23:05 PM UTC+1, lifo wrote: > We have a simple setup with 1 node, 1 index and 5 shards containing > totally about 110 million documents. Total index size is about 30Gb. All > documents are simply indexed by one field named ES\_VALUE and stores a value > named ES\_SUMMARY, in addition we have some other id fields indexed. We are > indexing several fields from our entities but they are all put into > ES\_VALUE when indexing, i.e. there are a lot more different values for > ES\_VALUE than there are documents in the index. > > A simple query for documents only using a wildcard query on ES\_VALUE > performs very different depending on how many documents its targeting. I.e. > when the result has a smaller total hit count it returns in a few mills but > when result has a large total hit count it takes about 10-15sec to return. > Size is set to 15 so there are only 15 documents actually returned to the > client. > > Any suggestions on how to get the queris to perform better with large > resultsets? -- ---
**Author:** ![Chris\_Male](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/chris_male/32/2607_2.png) [@Chris\_Male](https://discuss.elastic.co/u/Chris_Male)\ **Post date:** [November 15, 2012, 3:24am UTC](https://discuss.elastic.co/t/query-response-time-not-scaling-well-with-large-resultsets/9721/3 "2012-11-15T03:24:59Z")
Are you able to share with us your mappings and maybe an example of some queries you're executing? On Thursday, November 15, 2012 11:23:05 AM UTC+13, lifo wrote: > We have a simple setup with 1 node, 1 index and 5 shards containing > totally about 110 million documents. Total index size is about 30Gb. All > documents are simply indexed by one field named ES\_VALUE and stores a value > named ES\_SUMMARY, in addition we have some other id fields indexed. We are > indexing several fields from our entities but they are all put into > ES\_VALUE when indexing, i.e. there are a lot more different values for > ES\_VALUE than there are documents in the index. > > A simple query for documents only using a wildcard query on ES\_VALUE > performs very different depending on how many documents its targeting. I.e. > when the result has a smaller total hit count it returns in a few mills but > when result has a large total hit count it takes about 10-15sec to return. > Size is set to 15 so there are only 15 documents actually returned to the > client. > > Any suggestions on how to get the queris to perform better with large > resultsets? -- ---
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [November 15, 2012, 2:12pm UTC](https://discuss.elastic.co/t/query-response-time-not-scaling-well-with-large-resultsets/9721/4 "2012-11-15T14:12:52Z")
Just another quick remark to what you observe. Wildcard search is working by traversing the term list and looking for possibilities to expand the query matching the given pattern, and or'ing the set of terms before the query is run. The complexity of the wildcard search operation is not directly related to the number of documents in your index or to the number of hits in your result set, it is related to the total number of possible expansion forms of the terms in your index. In Lucene 4, with the finite state automaton queries, it is possible to "precompile" the terms in your index into an automaton, and this will reduce the time required to find and match all the word forms given by a regex to a minimum. Best regards, Jörg -- ---
**Author:** ![BillyEm](https://avatars.discourse-cdn.com/v4/letter/b/c4cdca/32.png) [@BillyEm](https://discuss.elastic.co/u/BillyEm)\ **Post date:** [November 15, 2012, 6:46pm UTC](https://discuss.elastic.co/t/query-response-time-not-scaling-well-with-large-resultsets/9721/5 "2012-11-15T18:46:14Z")
useconflations instead of wildcards. of course you'll have to build the support. the cost of a wildcard match on the socalled term list is negligble. although the postings list when very large is not optimized. some of Lucene 4 indexing scheme addresses this, if its been done right. i.e. allowing skipping. lastly, OR is used to enhance recall; if you're only pulling 15 documents you should be after precision and change your default operator to AND. This will also speed things up; probably quite noticeably. ignore the "auto-warm" statement. its not relevant to query performance; its a caching issue. On Wednesday, November 14, 2012 5:23:05 PM UTC-5, lifo wrote: > We have a simple setup with 1 node, 1 index and 5 shards containing > totally about 110 million documents. Total index size is about 30Gb. All > documents are simply indexed by one field named ES\_VALUE and stores a value > named ES\_SUMMARY, in addition we have some other id fields indexed. We are > indexing several fields from our entities but they are all put into > ES\_VALUE when indexing, i.e. there are a lot more different values for > ES\_VALUE than there are documents in the index. > > A simple query for documents only using a wildcard query on ES\_VALUE > performs very different depending on how many documents its targeting. I.e. > when the result has a smaller total hit count it returns in a few mills but > when result has a large total hit count it takes about 10-15sec to return. > Size is set to 15 so there are only 15 documents actually returned to the > client. > > Any suggestions on how to get the queris to perform better with large > resultsets? -- ---
**Author:** ![lifo](https://avatars.discourse-cdn.com/v4/letter/l/c77e96/32.png) [@lifo](https://discuss.elastic.co/u/lifo)\ **Post date:** [November 19, 2012, 7:58pm UTC](https://discuss.elastic.co/t/query-response-time-not-scaling-well-with-large-resultsets/9721/6 "2012-11-19T19:58:38Z")
Thanks for your answer. What does it really mean to change to AND as default operator? On Thursday, November 15, 2012 7:46:14 PM UTC+1, BillyEm wrote: > useconflations instead of wildcards. of course you'll have to build the > support. > > the cost of a wildcard match on the socalled term list is negligble. > although the postings list when very large is not optimized. some of Lucene > 4 indexing scheme addresses this, if its been done right. i.e. allowing > skipping. > > lastly, OR is used to enhance recall; if you're only pulling 15 documents > you should be after precision and change your default operator to AND. This > will also speed things up; probably quite noticeably. > > ignore the "auto-warm" statement. its not relevant to query performance; > its a caching issue. > > On Wednesday, November 14, 2012 5:23:05 PM UTC-5, lifo wrote: > > > We have a simple setup with 1 node, 1 index and 5 shards containing > > totally about 110 million documents. Total index size is about 30Gb. All > > documents are simply indexed by one field named ES\_VALUE and stores a value > > named ES\_SUMMARY, in addition we have some other id fields indexed. We are > > indexing several fields from our entities but they are all put into > > ES\_VALUE when indexing, i.e. there are a lot more different values for > > ES\_VALUE than there are documents in the index. > > > > A simple query for documents only using a wildcard query on ES\_VALUE > > performs very different depending on how many documents its targeting. I.e. > > when the result has a smaller total hit count it returns in a few mills but > > when result has a large total hit count it takes about 10-15sec to return. > > Size is set to 15 so there are only 15 documents actually returned to the > > client. > > > > Any suggestions on how to get the queris to perform better with large > > resultsets? -- ---
**Author:** ![lifo](https://avatars.discourse-cdn.com/v4/letter/l/c77e96/32.png) [@lifo](https://discuss.elastic.co/u/lifo)\ **Post date:** [November 19, 2012, 8:00pm UTC](https://discuss.elastic.co/t/query-response-time-not-scaling-well-with-large-resultsets/9721/7 "2012-11-19T20:00:16Z")
Thanks for the answers so far. If I understand this correctly the main reason for the the time consuming queries is the large amount of terms targeted and not the actual hit count, correct? I understand ES 0.21 has some new features thanks to Lucene 4, but what can be done now? More nodes, more or less number of shards, split up the index, update the mappings? Here are some queries end their execution time, and our mappings: [http://ourbox:9200/fruit/\_search?q=ES\_VALUE:201](http://ourbox:9200/fruit/_search?q=ES_VALUE:201)\* - _14s_ [http://ourbox:9200/fruit/\_search?q=ES\_VALUE:201206](http://ourbox:9200/fruit/_search?q=ES_VALUE:201206)\* - _600ms_ [http://ourbox:9200/fruit/\_search?q=ES\_VALUE:20120625XY123456](http://ourbox:9200/fruit/_search?q=ES_VALUE:20120625XY123456)\* - _100ms_ \* \* { "fruit" : { "Banana" : { "\_all" : { "enabled" : false }, "\_source" : { "excludes" : ["ES\_VALUE"] }, "properties" : { "ES\_DISTRIBUTOR" : { "type" : "string", "index" : "not\_analyzed", "omit\_norms" : true, "omit\_term\_freq\_and\_positions" : true }, "ES\_OWNER" : { "type" : "string", "index" : "not\_analyzed", "omit\_norms" : true, "omit\_term\_freq\_and\_positions" : true }, "ES\_SUMMARY" : { "type" : "string", "index" : "no", "store" : "yes" }, "ES\_VALUE" : { "type" : "string", "analyzer" : "fruitSimple" } } }, "Apple" : { "\_all" : { "enabled" : false }, "\_source" : { "excludes" : ["ES\_VALUE"] }, "properties" : { "ES\_DISTRIBUTOR" : { "type" : "string", "index" : "not\_analyzed", "omit\_norms" : true, "omit\_term\_freq\_and\_positions" : true }, "ES\_OWNER" : { "type" : "string", "index" : "not\_analyzed", "omit\_norms" : true, "omit\_term\_freq\_and\_positions" : true }, "ES\_SUMMARY" : { "type" : "string", "index" : "no", "store" : "yes" }, "ES\_VALUE" : { "type" : "string", "analyzer" : "fruitSimple" } } }, "Pineapple" : { "\_all" : { "enabled" : false }, "\_source" : { "excludes" : ["ES\_VALUE"] }, "properties" : { "ES\_DISTRIBUTOR" : { "type" : "string", "index" : "not\_analyzed", "omit\_norms" : true, "omit\_term\_freq\_and\_positions" : true }, "ES\_OWNER" : { "type" : "string", "index" : "not\_analyzed", "omit\_norms" : true, "omit\_term\_freq\_and\_positions" : true }, "ES\_SUMMARY" : { "type" : "string", "index" : "no", "store" : "yes" }, "ES\_VALUE" : { "type" : "string", "analyzer" : "fruitSimple" } } }, "Melon" : { "\_all" : { "enabled" : false }, "\_source" : { "excludes" : ["ES\_VALUE"] }, "properties" : { "ES\_DISTRIBUTOR" : { "type" : "string", "index" : "not\_analyzed", "omit\_norms" : true, "omit\_term\_freq\_and\_positions" : true }, "ES\_OWNER" : { "type" : "string", "index" : "not\_analyzed", "omit\_norms" : true, "omit\_term\_freq\_and\_positions" : true }, "ES\_SUMMARY" : { "type" : "string", "index" : "no", "store" : "yes" }, "ES\_VALUE" : { "type" : "string", "analyzer" : "fruitSimple" } } }, "Grapefruit" : { "\_all" : { "enabled" : false }, "\_source" : { "excludes" : ["ES\_VALUE"] }, "properties" : { "ES\_DISTRIBUTOR" : { "type" : "string", "index" : "not\_analyzed", "omit\_norms" : true, "omit\_term\_freq\_and\_positions" : true }, "ES\_OWNER" : { "type" : "string", "index" : "not\_analyzed", "omit\_norms" : true, "omit\_term\_freq\_and\_positions" : true }, "ES\_SUMMARY" : { "type" : "string", "index" : "no", "store" : "yes" }, "ES\_VALUE" : { "type" : "string", "analyzer" : "fruitSimple" }, } }, "Eggplant" : { "\_all" : { "enabled" : false }, "\_source" : { "excludes" : ["ES\_VALUE"] }, "properties" : { "ES\_DISTRIBUTOR" : { "type" : "string", "index" : "not\_analyzed", "omit\_norms" : true, "omit\_term\_freq\_and\_positions" : true }, "ES\_OWNER" : { "type" : "string", "index" : "not\_analyzed", "omit\_norms" : true, "omit\_term\_freq\_and\_positions" : true }, "ES\_SUMMARY" : { "type" : "string", "index" : "no", "store" : "yes" }, "ES\_VALUE" : { "type" : "string", "analyzer" : "fruitSimple" } } }, "Kiwi" : { "\_all" : { "enabled" : false }, "\_source" : { "excludes" : ["ES\_VALUE"] }, "properties" : { "ES\_DISTRIBUTOR" : { "type" : "string", "index" : "not\_analyzed", "omit\_norms" : true, "omit\_term\_freq\_and\_positions" : true }, "ES\_OWNER" : { "type" : "string", "index" : "not\_analyzed", "omit\_norms" : true, "omit\_term\_freq\_and\_positions" : true }, "ES\_SUMMARY" : { "type" : "string", "index" : "no", "store" : "yes" }, "ES\_VALUE" : { "type" : "string", "analyzer" : "fruitSimple" } } }, "Orange" : { "\_all" : { "enabled" : false }, "\_source" : { "excludes" : ["ES\_VALUE"] }, "properties" : { "ES\_DISTRIBUTOR" : { "type" : "string", "index" : "not\_analyzed", "omit\_norms" : true, "omit\_term\_freq\_and\_positions" : true }, "ES\_OWNER" : { "type" : "string", "index" : "not\_analyzed", "omit\_norms" : true, "omit\_term\_freq\_and\_positions" : true }, "ES\_SUMMARY" : { "type" : "string", "index" : "no", "store" : "yes" }, "ES\_VALUE" : { "type" : "string", "analyzer" : "fruitSimple" } } }, "Lemon" : { "\_all" : { "enabled" : false }, "\_source" : { "excludes" : ["ES\_VALUE"] }, "properties" : { "ES\_DISTRIBUTOR" : { "type" : "string", "index" : "not\_analyzed", "omit\_norms" : true, "omit\_term\_freq\_and\_positions" : true }, "ES\_OWNER" : { "type" : "string", "index" : "not\_analyzed", "omit\_norms" : true, "omit\_term\_freq\_and\_positions" : true }, "ES\_SUMMARY" : { "type" : "string", "index" : "no", "store" : "yes" }, "ES\_VALUE" : { "type" : "string", "analyzer" : "fruitSimple" } } }, "Lime" : { "\_all" : { "enabled" : false }, "\_source" : { "excludes" : ["ES\_VALUE"] }, "properties" : { "ES\_DISTRIBUTOR" : { "type" : "string", "index" : "not\_analyzed", "omit\_norms" : true, "omit\_term\_freq\_and\_positions" : true }, "ES\_OWNER" : { "type" : "string", "index" : "not\_analyzed", "omit\_norms" : true, "omit\_term\_freq\_and\_positions" : true }, "ES\_SUMMARY" : { "type" : "string", "index" : "no", "store" : "yes" }, "ES\_VALUE" : { "type" : "string", "analyzer" : "fruitSimple" } } }, } } On Wednesday, November 14, 2012 11:23:05 PM UTC+1, lifo wrote: > We have a simple setup with 1 node, 1 index and 5 shards containing > totally about 110 million documents. Total index size is about 30Gb. All > documents are simply indexed by one field named ES\_VALUE and stores a value > named ES\_SUMMARY, in addition we have some other id fields indexed. We are > indexing several fields from our entities but they are all put into > ES\_VALUE when indexing, i.e. there are a lot more different values for > ES\_VALUE than there are documents in the index. > > A simple query for documents only using a wildcard query on ES\_VALUE > performs very different depending on how many documents its targeting. I.e. > when the result has a smaller total hit count it returns in a few mills but > when result has a large total hit count it takes about 10-15sec to return. > Size is set to 15 so there are only 15 documents actually returned to the > client. > > Any suggestions on how to get the queris to perform better with large > resultsets? -- ---
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [November 19, 2012, 10:36pm UTC](https://discuss.elastic.co/t/query-response-time-not-scaling-well-with-large-resultsets/9721/8 "2012-11-19T22:36:51Z")
[http://ourbox:9200/fruit/\_search?q=ES\_VALUE:201\*](http://ourbox:9200/fruit/_search?q=ES_VALUE:201*) - _14s_ [http://ourbox:9200/fruit/\_search?q=ES\_VALUE:201206\*](http://ourbox:9200/fruit/_search?q=ES_VALUE:201206*) - _600ms_ [http://ourbox:9200/fruit/\_search?q=ES\_VALUE:20120625XY123456\*](http://ourbox:9200/fruit/_search?q=ES_VALUE:20120625XY123456*) - _100ms_ \* \* I would suggest to reconsider your query method by the given key "ES\_VALUE". Right now, you use "ES\_VALUE" as a compound key with the most expensive method, i.e. wildcard pattern: Estimate the total number of different "ES\_VALUE" values in your index (the cardinality of the value). I predict it is very high. The higher the cardinality, the worse the performance of a wildcard search. Note, ES is not a database, it has no b-tree index for fast wildcard prefix key lookups. One method to reconsider the query method is using a decomposed key. To me, it looks like it is possible to segment the key into components like year, month, day, and a numbering scheme. By having a number of fields for the key, you can combine them into a very fast boolean search, even over some integer values (something like 2012 and 06 and 25 and "XY123456" ...) A (cacheable) filter query with integer values of low cardinality is very fast in ES. Jörg -- ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 3:03am UTC](https://discuss.elastic.co/t/query-response-time-not-scaling-well-with-large-resultsets/9721/9 "2017-07-06T03:03:39Z")