{"action":"created","issue":{"url":"https://api.github.com/repos/arangodb/arangodb/issues/8165","repository_url":"https://api.github.com/repos/arangodb/arangodb","labels_url":"https://api.github.com/repos/arangodb/arangodb/issues/8165/labels{/name}","comments_url":"https://api.github.com/repos/arangodb/arangodb/issues/8165/comments","events_url":"https://api.github.com/repos/arangodb/arangodb/issues/8165/events","html_url":"https://github.com/arangodb/arangodb/issues/8165","id":410345965,"node_id":"MDU6SXNzdWU0MTAzNDU5NjU=","number":8165,"title":"AQL optimizer does not pick up multiple Geo index","user":{"login":"Levalis","id":9040600,"node_id":"MDQ6VXNlcjkwNDA2MDA=","avatar_url":"https://avatars0.githubusercontent.com/u/9040600?v=4","gravatar_id":"","url":"https://api.github.com/users/Levalis","html_url":"https://github.com/Levalis","followers_url":"https://api.github.com/users/Levalis/followers","following_url":"https://api.github.com/users/Levalis/following{/other_user}","gists_url":"https://api.github.com/users/Levalis/gists{/gist_id}","starred_url":"https://api.github.com/users/Levalis/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Levalis/subscriptions","organizations_url":"https://api.github.com/users/Levalis/orgs","repos_url":"https://api.github.com/users/Levalis/repos","events_url":"https://api.github.com/users/Levalis/events{/privacy}","received_events_url":"https://api.github.com/users/Levalis/received_events","type":"User","site_admin":false},"labels":[{"id":4539395,"node_id":"MDU6TGFiZWw0NTM5Mzk1","url":"https://api.github.com/repos/arangodb/arangodb/labels/1%20Bug","name":"1 Bug","color":"b60205","default":false},{"id":483469884,"node_id":"MDU6TGFiZWw0ODM0Njk4ODQ=","url":"https://api.github.com/repos/arangodb/arangodb/labels/2%20Fixed","name":"2 Fixed","color":"0052cc","default":false},{"id":5582373,"node_id":"MDU6TGFiZWw1NTgyMzcz","url":"https://api.github.com/repos/arangodb/arangodb/labels/3%20AQL","name":"3 AQL","color":"f9d0c4","default":false},{"id":1166894473,"node_id":"MDU6TGFiZWwxMTY2ODk0NDcz","url":"https://api.github.com/repos/arangodb/arangodb/labels/3%20Geo","name":"3 Geo","color":"f9d0c4","default":false},{"id":759969796,"node_id":"MDU6TGFiZWw3NTk5Njk3OTY=","url":"https://api.github.com/repos/arangodb/arangodb/labels/3%20Index","name":"3 Index","color":"f9d0c4","default":false}],"state":"closed","locked":false,"assignee":null,"assignees":[],"milestone":{"url":"https://api.github.com/repos/arangodb/arangodb/milestones/125","html_url":"https://github.com/arangodb/arangodb/milestone/125","labels_url":"https://api.github.com/repos/arangodb/arangodb/milestones/125/labels","id":4021778,"node_id":"MDk6TWlsZXN0b25lNDAyMTc3OA==","number":125,"title":"3.4.4","description":null,"creator":{"login":"kvahed","id":1295696,"node_id":"MDQ6VXNlcjEyOTU2OTY=","avatar_url":"https://avatars3.githubusercontent.com/u/1295696?v=4","gravatar_id":"","url":"https://api.github.com/users/kvahed","html_url":"https://github.com/kvahed","followers_url":"https://api.github.com/users/kvahed/followers","following_url":"https://api.github.com/users/kvahed/following{/other_user}","gists_url":"https://api.github.com/users/kvahed/gists{/gist_id}","starred_url":"https://api.github.com/users/kvahed/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/kvahed/subscriptions","organizations_url":"https://api.github.com/users/kvahed/orgs","repos_url":"https://api.github.com/users/kvahed/repos","events_url":"https://api.github.com/users/kvahed/events{/privacy}","received_events_url":"https://api.github.com/users/kvahed/received_events","type":"User","site_admin":false},"open_issues":0,"closed_issues":1,"state":"open","created_at":"2019-02-01T16:39:14Z","updated_at":"2019-02-19T16:10:59Z","due_on":null,"closed_at":null},"comments":1,"created_at":"2019-02-14T15:11:02Z","updated_at":"2019-02-19T16:10:58Z","closed_at":"2019-02-19T16:10:59Z","author_association":"NONE","body":"Hello, I'm facing a subtle issue with AQL and Geo indices. It seems the AQL query optimiser uses the Geo index once, then falls back to a full collection scan.\r\n\r\n## My Environment\r\n\r\n* __ArangoDB Version__:        3.4.2\r\n* __Storage Engine__:             RocksDB\r\n* __Deployment Mode__:       Single Server\r\n* __Deployment Strategy__:   ArangoDB Docker image (arangodb/arangodb:3.4.2)\r\n* __Configuration__:               default config\r\n* __Infrastructure__:               own\r\n* __Operating System__:        Ubuntu 18.04\r\n* __Total RAM in your machine__:        16GB\r\n* __Disks in use__:        SSD\r\n* __Used Package__:              Docker - official Docker library\r\n\r\n## Component, Query & Data\r\n\r\n__Affected feature__:\r\nAQL query using web interface\r\n\r\n\r\n__AQL query (if applicable)__:\r\n```\r\nLET col1 = (\r\n    FOR h in col1\r\n    FILTER GEO_DISTANCE(h.location, GEO_POINT(@lng, @lat)) <= 200\r\n    RETURN h\r\n)\r\nLET col2 = (\r\n    FOR e in col2\r\n    FILTER GEO_DISTANCE(e.location, GEO_POINT(@lng, @lat)) <= 200\r\n    RETURN e\r\n)\r\nRETURN { col1, col2 }\r\n```\r\n\r\n__AQL explain (if applicable)__:\r\n```\r\nExecution plan:\r\n Id   NodeType                  Est.   Comment\r\n  1   SingletonNode                1   * ROOT\r\n  7   SubqueryNode                 1     - LET col1 = ...   /* const subquery */\r\n  2   SingletonNode                1       * ROOT\r\n 16   IndexNode                    5         - FOR h IN col1   /* geo index scan */\r\n  6   ReturnNode                   5           - RETURN h\r\n 13   SubqueryNode                 1     - LET col2 = ...   /* const subquery */\r\n  8   SingletonNode                1       * ROOT\r\n  9   EnumerateCollectionNode      3         - FOR e IN col2   /* full collection scan */\r\n 10   CalculationNode              3           - LET #10 = (GEO_DISTANCE(e.`location`, { \"type\" : \"Point\", \"coordinates\" : [ 4.857288, 50.465768 ] }) <= 200)   /* simple expression */   /* collections used: e : col2 */\r\n 11   FilterNode                   3           - FILTER #10\r\n 12   ReturnNode                   3           - RETURN e\r\n 14   CalculationNode              1     - LET #12 = { \"col1\" : col1, \"col2\" : col2 }   /* simple expression */\r\n 15   ReturnNode                   1     - RETURN #12\r\n\r\nIndexes used:\r\n By   Type   Collection   Unique   Sparse   Selectivity   Fields           Ranges\r\n 16   geo    col1      false    true             n/a   [ `location` ]   (GEO_DISTANCE({ \"type\" : \"Point\", \"coordinates\" : [ 4.857288, 50.465768 ] }, h.`location`) <= 200)\r\n\r\nFunctions used:\r\n Name           Deterministic   Cacheable   Uses V8\r\n GEO_DISTANCE   true            true        false  \r\n\r\nOptimization rules applied:\r\n Id   RuleName\r\n  1   geo-index-optimizer\r\n  2   remove-unnecessary-calculations-2\r\n```\r\n\r\n__Size of your Dataset on disk__:\r\nA couple megabytes\r\n\r\n## Steps to reproduce\r\n\r\n1. Create Geo indices on col1 and col2 (\"location\" field)\r\n2. Run query in web interface\r\n3. Profile query\r\n\r\n__Problem__:\r\nThe Geo index of col2 is not used\r\n\r\nYou can switch the query around like this\r\n```\r\nLET col2 = (\r\n    FOR h in col2\r\n    FILTER GEO_DISTANCE(h.location, GEO_POINT(@lng, @lat)) <= 200\r\n    RETURN h\r\n)\r\nLET col1 = (\r\n    FOR e in col1\r\n    FILTER GEO_DISTANCE(e.location, GEO_POINT(@lng, @lat)) <= 200\r\n    RETURN e\r\n)\r\nRETURN { col1, col2 }\r\n```\r\nand it will use the Geo index for col2 then fall back to full collection scan for col1.\r\n\r\n__Expected result__:\r\nThe Geo index is used for col1 and col2\r\n\r\nI've made sure both Geo indices are set up and work for a simple query (like below)\r\n```\r\nFOR h in col2\r\nFILTER GEO_DISTANCE(h.location, GEO_POINT(@lng, @lat)) <= 200\r\nRETURN h\r\n```"},"comment":{"url":"https://api.github.com/repos/arangodb/arangodb/issues/comments/465197006","html_url":"https://github.com/arangodb/arangodb/issues/8165#issuecomment-465197006","issue_url":"https://api.github.com/repos/arangodb/arangodb/issues/8165","id":465197006,"node_id":"MDEyOklzc3VlQ29tbWVudDQ2NTE5NzAwNg==","user":{"login":"Levalis","id":9040600,"node_id":"MDQ6VXNlcjkwNDA2MDA=","avatar_url":"https://avatars0.githubusercontent.com/u/9040600?v=4","gravatar_id":"","url":"https://api.github.com/users/Levalis","html_url":"https://github.com/Levalis","followers_url":"https://api.github.com/users/Levalis/followers","following_url":"https://api.github.com/users/Levalis/following{/other_user}","gists_url":"https://api.github.com/users/Levalis/gists{/gist_id}","starred_url":"https://api.github.com/users/Levalis/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Levalis/subscriptions","organizations_url":"https://api.github.com/users/Levalis/orgs","repos_url":"https://api.github.com/users/Levalis/repos","events_url":"https://api.github.com/users/Levalis/events{/privacy}","received_events_url":"https://api.github.com/users/Levalis/received_events","type":"User","site_admin":false},"created_at":"2019-02-19T16:10:58Z","updated_at":"2019-02-19T16:10:58Z","author_association":"NONE","body":"Thanks for the update. "}}
{"actor":{"display_login":"Levalis"}}