title: Neo4j - cypher query optimization
body: <p>I am pretty new with Neo4j and we are trying to use it with our PHP SQL Server based application. I am using Neo4j 2.0 milestone 6. Some of the relevant configuration variables:</p>
  
  <pre><code>wrapper.java.initmemory=5000
  wrapper.java.maxmemory=5000
  </code></pre>
  
  <p>Now coming to the question- I am trying to write a cypher query which traverses graph and calculates allocation amount. Here is a snapshot of the structure of the graph. </p>
  
  <p><img src="https://i.stack.imgur.com/j5EVr.png" alt="enter image description here"></p>
  
  <p>Basically I have a department as a starting point which has certain amount and then it is allocated out to certain Product which in turn allocates out to other Products, so on. It can be n level deep allocation. Now I need to calculate amount for all the products.</p>
  
  <p>The cypher query that I am using is as below:</p>
  
  <pre><code>MATCH (f:financial) WHERE f.amount &lt;&gt; 0 
  WITH f
  MATCH f-[r_allocates:Allocates*]-&gt;(n_allocation)
  RETURN 
      n_allocation.cost_pool_hierarchy_id,  
      SUM(reduce(totalamt=f.amount, r IN r_allocates| (r.allocate)*totalamt/100 )) as amt 
  ORDER BY n_allocation.cost_pool_hierarchy_id 
  LIMIT 1000
  </code></pre>
  
  <p>This query takes 30+ seconds with warmed up caches. I have tried going back to Neo4j 1.9 as I found on some posts that Neo4j 2.0 is not yet optimized, but the similar query in 1.9 takes 40+ seconds.</p>
  
  <p>Here is the output from profiler:</p>
  
  <pre><code>==&gt; ColumnFilter(symKeys=["n_allocation.cost_pool_hierarchy_id", "  INTERNAL_AGGREGATE2db722c4-6400-4803-90a2-b883b0076e8b"], returnItemNames=["n_allocation.cost_pool_hierarchy_id", "amt"], _rows=746, _db_hits=0)
  ==&gt; Top(orderBy=["SortItem(Cached(n_allocation.cost_pool_hierarchy_id of type Any),true)"], limit="Literal(1000)", _rows=746, _db_hits=0)
  ==&gt;   EagerAggregation(keys=["Cached(n_allocation.cost_pool_hierarchy_id of type Any)"], aggregates=["(  INTERNAL_AGGREGATE2db722c4-6400-4803-90a2-b883b0076e8b,Sum(ReduceFunction(r_allocates,r,Divide(Multiply(Product(r,allocate(11),true),totalamt),Literal(100)),totalamt,Product(f,amount(9),true))))"], _rows=746, _db_hits=11680622)
  ==&gt;     Extract(symKeys=["f", "n_allocation", "  UNNAMED55", "r_allocates"], exprKeys=["n_allocation.cost_pool_hierarchy_id"], _rows=3906768, _db_hits=3906768)
  ==&gt;       PatternMatch(g="(f)-['  UNNAMED55']-(n_allocation)", _rows=3906768, _db_hits=0)
  ==&gt;         Filter(pred="(NOT(Product(f,amount(9),true) == Literal(0)) AND hasLabel(f:financial(6)))", _rows=9959, _db_hits=34272)
  ==&gt;           NodeByLabel(label="financial", identifier="f", _rows=34272, _db_hits=0)
  </code></pre>
  
  <p>I would appreciate any help in optimizing this query. Do I need to update some configuration settings? Or do I need to change the structure of graph?</p>
  
  <p><strong>Update:</strong>
  Just to add further - even the below relatively simple query takes 26 seconds:</p>
  
  <pre><code>MATCH p=(f:financial)-[r*2]-&gt;(n)  RETURN COUNT(p)
  </code></pre>