{"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":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":3,"created_at":"2015-04-14T20:46:42Z","updated_at":"2015-04-15T22:28:29Z","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/93587058","html_url":"https://github.com/neo4j/neo4j/issues/4435#issuecomment-93587058","issue_url":"https://api.github.com/repos/neo4j/neo4j/issues/4435","id":93587058,"user":{"login":"jexp","id":67427,"avatar_url":"https://avatars.githubusercontent.com/u/67427?v=3","gravatar_id":"","url":"https://api.github.com/users/jexp","html_url":"https://github.com/jexp","followers_url":"https://api.github.com/users/jexp/followers","following_url":"https://api.github.com/users/jexp/following{/other_user}","gists_url":"https://api.github.com/users/jexp/gists{/gist_id}","starred_url":"https://api.github.com/users/jexp/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jexp/subscriptions","organizations_url":"https://api.github.com/users/jexp/orgs","repos_url":"https://api.github.com/users/jexp/repos","events_url":"https://api.github.com/users/jexp/events{/privacy}","received_events_url":"https://api.github.com/users/jexp/received_events","type":"User","site_admin":false},"created_at":"2015-04-15T22:28:29Z","updated_at":"2015-04-15T22:28:29Z","body":"Hey Bill, sorry you ran into this, it's a bug which will be fixed.\r\n\r\nCurrently computed expressions are not taken into account for index lookups in the cost planner.\r\n\r\nBut you can force it to use an index, I'd be interested in the comparison between cost and rule then:\r\n\r\n````\r\n  PROFILE\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 = cr.teamid\r\n  RETURN p\r\n````"}}