# Super slow bool query with terms filter array **URL:** \ **Category:** Elasticsearch\ **Created:** [November 13, 2018, 9:19pm UTC](https://discuss.elastic.co/t/super-slow-bool-query-with-terms-filter-array/156549 "2018-11-13T21:19:29Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Teddis](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/teddis/32/27902_2.png) [@Teddis](https://discuss.elastic.co/u/Teddis)\ **Post date:** [November 13, 2018, 9:19pm UTC](https://discuss.elastic.co/t/super-slow-bool-query-with-terms-filter-array/156549/1 "2018-11-13T21:19:29Z")
My query is running insanely slow. This one is over 1.3 seconds with just one should matches and I need 3-4 more which can take it up to 14-15 seconds! First I run a store search to yield a set of menuIds to filter against in an items index. Shouldn't ES perform the filter first and then search against those documents. My index has over 3 million documents which doesn't seem long and I'm only searching against one or a few fields which are indexed. It almost seems like a full text search is occurring without index. Could the synonyms included in the mapping be contributing this problem? Here is a link to the mapping and long profile query result: [https://drive.google.com/open?id=1yaFFKutDYiwlTdjseZZqIijCrUXncyzf](https://drive.google.com/open?id=1yaFFKutDYiwlTdjseZZqIijCrUXncyzf) Here's the query: ```auto GET /item/_doc/_search { "profile": true, "query": { "bool": { "filter": { "terms": { "menuId": [ "1362806", "1220659", "1220282", "1410886", "1408407", "1220203", "1362729", "1389199", "1230880", "1222243", "1226957", "1187670", "1226979", "1223458", "1222824", "1303641", "1220260", "1226806", "1226635", "1226987", "1187857", "1224780", "1345293", "1367150", "1219483", "1367476", "1265920", "1226960", "1367486", "1222986", "1409449", "1223819", "1227004", "1396947", "1396899", "1226976", "1409786", "1220517", "1280015", "1219434", "1409491", "1307692", "1219721", "1410684", "1227037", "1401613", "1221572", "1224560", "1223365", "1411075", "1225831", "1410090", "1374307", "1255994", "1226986", "1363398", "1227332", "1410279", "1401560", "1409946", "1226996", "1399023", "1253851", "1410280", "1221602", "1268223", "1226394", "1219534", "1304872", "1410880", "1372924", "1307391", "1227826", "1223129", "1393406", "1222275", "1409495", "1295376", "1347656", "1410078", "1220572", "1311836", "1409484", "1410077", "1295066", "1305062", "1214538", "1409485", "1219827", "1304871", "1363298", "1224902", "1410505", "1279475", "1222201", "1225956", "1386873", "1221805", "1220307", "1226974", "1222328", "1410485", "1221940", "1253502", "1399371" ] } }, "minimum_should_match": 1, "should": [ { "match": { "name": { "query": "chicken pot pie", "operator": "and", "fuzziness": "AUTO", "prefix_length": 2, "boost": 1100 } } } ] } } } ``` ---
**Author:** ![ddorian43](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ddorian43/32/36093_2.png) [@ddorian43](https://discuss.elastic.co/u/ddorian43)\ **Post date:** [November 13, 2018, 10:45pm UTC](https://discuss.elastic.co/t/super-slow-bool-query-with-terms-filter-array/156549/2 "2018-11-13T22:45:07Z")
Try profiling. It's probably the `terms` filter. ---
**Author:** ![Teddis](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/teddis/32/27902_2.png) [@Teddis](https://discuss.elastic.co/u/Teddis)\ **Post date:** [November 14, 2018, 2:38am UTC](https://discuss.elastic.co/t/super-slow-bool-query-with-terms-filter-array/156549/3 "2018-11-14T02:38:39Z")
Thanks, yes, I profiled it and supplied the profile output in a link above. 1. I moved the filter into a "must" clause and removed the filter: ```auto { "must": { "terms": { "menuId": [ "1246", "124", "103", "113", "124", "135", "1239", "1239", "217", "116", "163", "116", "28", "97", "206", "1340", "1064", "103", "3", "1094", "111", "61", "222", "111", "225", "156", "40950", "1087", "62", "229", "1339", "62", "1245", "231", "62" ] } } ``` 1. Same horrible slowness. 2. I tried removing all but one term in the query and still horribly bad. 3. I tried removing the terms query entirely and still bad. 4. I wonder if any of the Lucene indices are bad. 5. Note that when I perform a reindex on this index, it oddly finished prematurely and looses 95% of the data! And the mapping hasn't changed, except for synonym settings. 6. When I run the same query against a 2nd duplicate cloud deployment with 1/10 the index size of data, it performs in 30ms, so the first is 500x slower! Very strange behavior is concerning as we depend on ES now for our search solution. ---
**Author:** ![ddorian43](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ddorian43/32/36093_2.png) [@ddorian43](https://discuss.elastic.co/u/ddorian43)\ **Post date:** [November 14, 2018, 8:31am UTC](https://discuss.elastic.co/t/super-slow-bool-query-with-terms-filter-array/156549/4 "2018-11-14T08:31:18Z")
Open access to the gdrive link. ---
**Author:** ![Teddis](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/teddis/32/27902_2.png) [@Teddis](https://discuss.elastic.co/u/Teddis)\ **Post date:** [November 14, 2018, 4:33pm UTC](https://discuss.elastic.co/t/super-slow-bool-query-with-terms-filter-array/156549/5 "2018-11-14T16:33:55Z")
Sorry, access should be open now. ---
**Author:** ![Teddis](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/teddis/32/27902_2.png) [@Teddis](https://discuss.elastic.co/u/Teddis)\ **Post date:** [November 14, 2018, 4:42pm UTC](https://discuss.elastic.co/t/super-slow-bool-query-with-terms-filter-array/156549/6 "2018-11-14T16:42:58Z")
I'm still pretty novice with ES and I believe my deployment shards became damaged. When I created a fresh deployment and reinserted my docs, the search dropped to 600-700ms. Same query, same data. Very strange, I wish I understood why. But the query time is still too slow. ---
**Author:** ![Teddis](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/teddis/32/27902_2.png) [@Teddis](https://discuss.elastic.co/u/Teddis)\ **Post date:** [November 26, 2018, 5:11pm UTC](https://discuss.elastic.co/t/super-slow-bool-query-with-terms-filter-array/156549/7 "2018-11-26T17:11:20Z")
@ddorian43 Hi, were you able to take a look? I'm floored that a simple query with even just one must term query clause is slowing down a search of 2-6 million documents. Is there anyone who can take a critical look at our mapping and query DSL to optimize or point out flaws? ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [December 24, 2018, 5:24pm UTC](https://discuss.elastic.co/t/super-slow-bool-query-with-terms-filter-array/156549/8 "2018-12-24T17:24:51Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.