{"action":"created","issue":{"url":"https://api.github.com/repos/arangodb/arangodb/issues/3742","repository_url":"https://api.github.com/repos/arangodb/arangodb","labels_url":"https://api.github.com/repos/arangodb/arangodb/issues/3742/labels{/name}","comments_url":"https://api.github.com/repos/arangodb/arangodb/issues/3742/comments","events_url":"https://api.github.com/repos/arangodb/arangodb/issues/3742/events","html_url":"https://github.com/arangodb/arangodb/issues/3742","id":275162005,"number":3742,"title":"Indexes not used","user":{"login":"Thijxx","id":3355823,"avatar_url":"https://avatars2.githubusercontent.com/u/3355823?v=4","gravatar_id":"","url":"https://api.github.com/users/Thijxx","html_url":"https://github.com/Thijxx","followers_url":"https://api.github.com/users/Thijxx/followers","following_url":"https://api.github.com/users/Thijxx/following{/other_user}","gists_url":"https://api.github.com/users/Thijxx/gists{/gist_id}","starred_url":"https://api.github.com/users/Thijxx/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Thijxx/subscriptions","organizations_url":"https://api.github.com/users/Thijxx/orgs","repos_url":"https://api.github.com/users/Thijxx/repos","events_url":"https://api.github.com/users/Thijxx/events{/privacy}","received_events_url":"https://api.github.com/users/Thijxx/received_events","type":"User","site_admin":false},"labels":[{"id":699898031,"url":"https://api.github.com/repos/arangodb/arangodb/labels/1%20Analyzing","name":"1 Analyzing","color":"1d76db","default":false},{"id":698202371,"url":"https://api.github.com/repos/arangodb/arangodb/labels/2%20Solved","name":"2 Solved","color":"0052cc","default":false},{"id":652112425,"url":"https://api.github.com/repos/arangodb/arangodb/labels/3%20Optimizer","name":"3 Optimizer","color":"f9d0c4","default":false},{"id":4539396,"url":"https://api.github.com/repos/arangodb/arangodb/labels/performance","name":"performance","color":"5319e7","default":false}],"state":"closed","locked":false,"assignee":{"login":"Simran-B","id":7819991,"avatar_url":"https://avatars3.githubusercontent.com/u/7819991?v=4","gravatar_id":"","url":"https://api.github.com/users/Simran-B","html_url":"https://github.com/Simran-B","followers_url":"https://api.github.com/users/Simran-B/followers","following_url":"https://api.github.com/users/Simran-B/following{/other_user}","gists_url":"https://api.github.com/users/Simran-B/gists{/gist_id}","starred_url":"https://api.github.com/users/Simran-B/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Simran-B/subscriptions","organizations_url":"https://api.github.com/users/Simran-B/orgs","repos_url":"https://api.github.com/users/Simran-B/repos","events_url":"https://api.github.com/users/Simran-B/events{/privacy}","received_events_url":"https://api.github.com/users/Simran-B/received_events","type":"User","site_admin":false},"assignees":[{"login":"Simran-B","id":7819991,"avatar_url":"https://avatars3.githubusercontent.com/u/7819991?v=4","gravatar_id":"","url":"https://api.github.com/users/Simran-B","html_url":"https://github.com/Simran-B","followers_url":"https://api.github.com/users/Simran-B/followers","following_url":"https://api.github.com/users/Simran-B/following{/other_user}","gists_url":"https://api.github.com/users/Simran-B/gists{/gist_id}","starred_url":"https://api.github.com/users/Simran-B/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Simran-B/subscriptions","organizations_url":"https://api.github.com/users/Simran-B/orgs","repos_url":"https://api.github.com/users/Simran-B/repos","events_url":"https://api.github.com/users/Simran-B/events{/privacy}","received_events_url":"https://api.github.com/users/Simran-B/received_events","type":"User","site_admin":false}],"milestone":null,"comments":5,"created_at":"2017-11-19T14:12:53Z","updated_at":"2017-11-21T09:57:17Z","closed_at":"2017-11-21T09:57:17Z","author_association":"NONE","body":"## my environment running ArangoDB\r\nI'm using the latest ArangoDB of the respective release series:\r\n- [X] 3.2\r\n\r\n\r\nMode:\r\n- [X] Single-Server\r\n\r\nStorage-Engine:\r\n- [X] rocksdb\r\n\r\nOn this operating system:\r\n  - [X] RedHat .rpm\r\n\r\nWhy does Arango not use the skip_list indexes resulting in an extremely slow query?\r\n\r\n**Query**\r\n```\r\nfor i in -14..-1\r\nlet fromDate = DATE_ADD(DATE_NOW(), i, \"days\")\r\nlet toDate = DATE_ADD(DATE_NOW(), i + 1, \"days\")\r\nlet avgValue = (\r\n    for doc in incoming\r\n    filter doc.tickle.received > fromDate  && doc.tickle.received < toDate && doc.tickle.id == 42\r\n    collect aggregate avgValue = AVG(doc.tickle.value2)\r\n    return avgValue)\r\nreturn 100-(avgValue/10)\r\n```\r\n\r\n**Explain**\r\n```\r\nQuery string:\r\n for i in -14..-1\r\n let fromDate = DATE_ADD(DATE_NOW(), i, \"days\")\r\n let toDate = DATE_ADD(DATE_NOW(), i + 1, \"days\")\r\n let avgValue = (\r\n     for doc in incoming\r\n     filter doc.tickle.received > fromDate  && doc.tickle.received < toDate && doc.tickle.id == 42\r\n     collect aggregate avgValue = AVG(doc.tickle.value2)\r\n     return avgValue)\r\n return 100-(avgValue/10)\r\n\r\nExecution plan:\r\n Id   NodeType             Est.   Comment\r\n  1   SingletonNode           1   * ROOT\r\n  2   CalculationNode         1     - LET #8 = -14 .. -1   /* range */   /* simple expression */\r\n  3   EnumerateListNode      14     - FOR i IN #8   /* list iteration */\r\n  4   CalculationNode        14       - LET fromDate = DATE_ADD(DATE_NOW(), i, \"days\")   /* v8 expression */\r\n  5   CalculationNode        14       - LET toDate = DATE_ADD(DATE_NOW(), (i + 1), \"days\")   /* v8 expression */\r\n 13   SubqueryNode           14       - LET avgValue = ...   /* subquery */\r\n  6   SingletonNode           1         * ROOT\r\n 16   IndexNode           78997           - FOR doc IN incoming   /* hash index scan */\r\n 17   CalculationNode     78997             - LET #10 = ((doc.`tickle`.`received` > fromDate) && (doc.`tickle`.`received` < toDate))   /* simple expression */   /* collections used: doc : incoming */\r\n  9   FilterNode          78997             - FILTER #10\r\n 10   CalculationNode     78997             - LET #12 = doc.`tickle`.`value2`   /* attribute expression */   /* collections used: doc : incoming */\r\n 11   CollectNode         63197             - COLLECT AGGREGATE avgValue = AVG(#12)   /* sorted */\r\n 12   ReturnNode          63197             - RETURN avgValue\r\n 14   CalculationNode        14       - LET #14 = (100 - (avgValue / 10))   /* simple expression */\r\n 15   ReturnNode             14       - RETURN #14\r\n\r\nIndexes used:\r\n By   Type   Collection   Unique   Sparse   Selectivity   Fields            Ranges\r\n 16   hash   incoming     false    false         0.00 %   [ `tickle.id` ]   (doc.`tickle`.`id` == 42)\r\n\r\nOptimization rules applied:\r\n Id   RuleName\r\n  1   move-calculations-up\r\n  2   move-filters-up\r\n  3   move-calculations-up-2\r\n  4   move-filters-up-2\r\n  5   use-indexes\r\n  6   remove-filter-covered-by-index\r\n\r\n```\r\n\r\n**Indexes**\r\n![screenshot from 2017-11-19 15-10-50](https://user-images.githubusercontent.com/3355823/32991429-e29921b6-cd3b-11e7-9e24-6a8eabbab3d2.png)\r\n"},"comment":{"url":"https://api.github.com/repos/arangodb/arangodb/issues/comments/345975440","html_url":"https://github.com/arangodb/arangodb/issues/3742#issuecomment-345975440","issue_url":"https://api.github.com/repos/arangodb/arangodb/issues/3742","id":345975440,"user":{"login":"ObiWahn","id":741109,"avatar_url":"https://avatars1.githubusercontent.com/u/741109?v=4","gravatar_id":"","url":"https://api.github.com/users/ObiWahn","html_url":"https://github.com/ObiWahn","followers_url":"https://api.github.com/users/ObiWahn/followers","following_url":"https://api.github.com/users/ObiWahn/following{/other_user}","gists_url":"https://api.github.com/users/ObiWahn/gists{/gist_id}","starred_url":"https://api.github.com/users/ObiWahn/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ObiWahn/subscriptions","organizations_url":"https://api.github.com/users/ObiWahn/orgs","repos_url":"https://api.github.com/users/ObiWahn/repos","events_url":"https://api.github.com/users/ObiWahn/events{/privacy}","received_events_url":"https://api.github.com/users/ObiWahn/received_events","type":"User","site_admin":false},"created_at":"2017-11-21T09:57:17Z","updated_at":"2017-11-21T09:57:17Z","author_association":"CONTRIBUTOR","body":"solved by @jsteemann "}}
{"actor":{"display_login":"ObiWahn"}}