{"action":"created","issue":{"url":"https://api.github.com/repos/neo4j/neo4j/issues/4435","labels_url":"https://api.github.com/repos/neo4j/neo4j/issues/4435/labels{/name}","comments_url":"https://api.github.com/repos/neo4j/neo4j/issues/4435/comments","events_url":"https://api.github.com/repos/neo4j/neo4j/issues/4435/events","html_url":"https://github.com/neo4j/neo4j/issues/4435","id":68492359,"number":4435,"title":"Neo4j 2.2 Cypher - Cost planner not using indexes for computed values, index hints not working","user":{"login":"bunkat","id":665885,"avatar_url":"https://avatars.githubusercontent.com/u/665885?v=3","gravatar_id":"","url":"https://api.github.com/users/bunkat","html_url":"https://github.com/bunkat","followers_url":"https://api.github.com/users/bunkat/followers","following_url":"https://api.github.com/users/bunkat/following{/other_user}","gists_url":"https://api.github.com/users/bunkat/gists{/gist_id}","starred_url":"https://api.github.com/users/bunkat/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/bunkat/subscriptions","organizations_url":"https://api.github.com/users/bunkat/orgs","repos_url":"https://api.github.com/users/bunkat/repos","events_url":"https://api.github.com/users/bunkat/events{/privacy}","received_events_url":"https://api.github.com/users/bunkat/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/neo4j/neo4j/labels/cypher","name":"cypher","color":"009800"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":9,"created_at":"2015-04-14T20:46:42Z","updated_at":"2015-10-09T14:26:37Z","closed_at":null,"body":"The COST planner is unable to use indexes for basic MATCH statements, instead choosing to do expensive NodeByLabelScan operations.  This seems to be a problem with using indexes on computed values (cr.teamid) vs with parameters ({p0}) or static strings (\"foobar\"). I also noticed that the COST planner uses a NodeIndexSeek operation instead of a SchemaIndex operation (for example, when finding the user in the below queries).  It is unclear what the difference between the two is.\r\n\r\nThe RULE planner uses the SchemaIndex correctly in both instances.\r\n\r\n**Query** \r\n\r\n```` \r\n  PROFILE\r\n  PLANNER RULE\r\n  MATCH (u:user {id: \"foobar\"})\r\n  WITH u\r\n\r\n  MATCH (u)-[:CONTACT]->(c:contact)-[:CONTACT]->(cr:role)\r\n  WHERE HAS(cr.teamid)\r\n  WITH c, cr\r\n\r\n  MATCH (p:project)\r\n  WHERE p.teamid = cr.teamid\r\n  RETURN p\r\n````\r\n\r\n***Result***\r\n\r\n![image](https://cloud.githubusercontent.com/assets/665885/7146829/20d5cf3e-e2ab-11e4-8bc4-ac2f098c5276.png)\r\n\r\n**Query** \r\n\r\n```` \r\n  PROFILE\r\n  PLANNER COST\r\n  MATCH (u:user {id: \"foobar\"})\r\n  WITH u\r\n\r\n  MATCH (u)-[:CONTACT]->(c:contact)-[:CONTACT]->(cr:role)\r\n  WHERE HAS(cr.teamid)\r\n  WITH c, cr\r\n\r\n  MATCH (p:project)\r\n  WHERE p.teamid = cr.teamid\r\n  RETURN p\r\n````\r\n\r\n***Result***\r\n\r\n![image](https://cloud.githubusercontent.com/assets/665885/7146851/502734da-e2ab-11e4-85a8-6a0044358254.png)\r\n\r\n**Query** \r\n\r\n```` \r\n  PROFILE\r\n  PLANNER COST\r\n  MATCH (u:user {id: \"foobar\"})\r\n  WITH u\r\n\r\n  MATCH (u)-[:CONTACT]->(c:contact)-[:CONTACT]->(cr:role)\r\n  WHERE HAS(cr.teamid)\r\n  WITH c, cr\r\n\r\n  MATCH (p:project)\r\n  USING INDEX p:project(teamid)\r\n  WHERE p.teamid IN [cr.teamid]\r\n  RETURN p\r\n````\r\n\r\n***Result***\r\n\r\n![image](https://cloud.githubusercontent.com/assets/665885/7146884/96cce45c-e2ab-11e4-9c44-5edfa577511e.png)"},"comment":{"url":"https://api.github.com/repos/neo4j/neo4j/issues/comments/146886478","html_url":"https://github.com/neo4j/neo4j/issues/4435#issuecomment-146886478","issue_url":"https://api.github.com/repos/neo4j/neo4j/issues/4435","id":146886478,"user":{"login":"jjaderberg","id":6028968,"avatar_url":"https://avatars.githubusercontent.com/u/6028968?v=3","gravatar_id":"","url":"https://api.github.com/users/jjaderberg","html_url":"https://github.com/jjaderberg","followers_url":"https://api.github.com/users/jjaderberg/followers","following_url":"https://api.github.com/users/jjaderberg/following{/other_user}","gists_url":"https://api.github.com/users/jjaderberg/gists{/gist_id}","starred_url":"https://api.github.com/users/jjaderberg/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jjaderberg/subscriptions","organizations_url":"https://api.github.com/users/jjaderberg/orgs","repos_url":"https://api.github.com/users/jjaderberg/repos","events_url":"https://api.github.com/users/jjaderberg/events{/privacy}","received_events_url":"https://api.github.com/users/jjaderberg/received_events","type":"User","site_admin":false},"created_at":"2015-10-09T14:26:37Z","updated_at":"2015-10-09T14:26:37Z","body":"I see a similar problem where the cost based planner fails with index hints. In my case the values are string literals, not properties or expressions, so not sure if it's the same problem.\r\n\r\nExecuting the following query with the cost planner in Cypher 2.2 gives various exceptions with the message `The current planner cannot satisfy all hints in the query, please try removing hints or try with another planner`.\r\n\r\n    MATCH (a:Tag)-[:RELATED*1..2]-t-[:RELATED*1..2]-(b:Tag), t-[:RELATED*1..2]-(c:Tag)\r\n    USING INDEX a:Tag(name)\r\n    USING INDEX b:Tag(name)\r\n    USING INDEX c:Tag(name)\r\n    WHERE a.name = \"A\" AND b.name = \"B\" AND c.name = \"C\"\r\n    RETURN DISTINCT t\r\n\r\nIn  the shell I get a `QueryExecutionKernelException`.\r\n![shell-22-cost](https://cloud.githubusercontent.com/assets/6028968/10395723/c1cf15f6-6e9e-11e5-8dda-487334d5620a.png)\r\n\r\nThe browser gives a `Neo.ClientError.Schema.NoSuchIndex`, but the message is the same.\r\n![browser-22-cost](https://cloud.githubusercontent.com/assets/6028968/10395720/c1c98eec-6e9e-11e5-9b78-b8f0bdcace85.png)\r\n\r\nI set up a [sample graph][1] in a Rabbithole console. There I get a `HintException`, but again the message is the same.\r\n![console-22-cost](https://cloud.githubusercontent.com/assets/6028968/10395721/c1ca8586-6e9e-11e5-91b1-8263bdfe710a.png)\r\n\r\nThe same query works fine with the cost planner in the 2.3 compiler in the console.\r\n![console-23-cost](https://cloud.githubusercontent.com/assets/6028968/10395722/c1cd6300-6e9e-11e5-99e2-6e1f1e119af3.png)\r\n\r\nIt also works with Cypher 2.2 if I force the rule planner.\r\n![console-22-rule](https://cloud.githubusercontent.com/assets/6028968/10395724/c1cf8446-6e9e-11e5-89f8-e86f97596cdb.png)\r\n\r\n[1]: http://console.neo4j.org/r/2mi9fv\r\n"}}