Learn Five Secrets to More Effective Query Tuning with Neo4j 2.2.x

Even in Neo4j with its high performance graph traversals, there are always queries that could and should be run faster – especially if your data is highly connected and if global pattern matches make even a single query account for many millions or billions of paths.

For this article, we’re using the larger movie dataset, which is also listed on the example datasets page.

The domain model that interests us here, is pretty straightforward:

(:Person {name}) -[:ACTS_IN|:DIRECTED]-> (:Movie {title})
(:Movie {title}) -[:GENRE]-> (:Genre {name})


HARDWARE


I presume you use a sensible machine, with a SSD (or enough IOPS) and decent amount of RAM. For a highly concurrent load, there should be also enough CPU cores to handle it.

Other questions to consider: Did you monitor io-waits, top, for CPU and memory usage? Any bottlenecks that turn up?

If so, you should address those issues first.

On Linux, configure your disk scheduler to noop or deadline and mount the database volume with noatime. See this blog post for more information.

CONFIG


For best results, use the latest stable version of Neo4j (i.e., Neo4j Enterprise 2.2.5). There is always an Enterprise trial version available to give you a high-watermark baseline, so compare it to Neo4j Community on your machine as needed.

Set dbms.pagecache.memory=4G in conf/neo4j.properties or the size of the store-files (nodes, relationships, properties, string-properties) combined.

ls -lt data/graph.db/neostore.*.db
3802094 16 Jul 14:31 data/graph.db/neostore.propertystore.db
 456960 16 Jul 14:31 data/graph.db/neostore.relationshipstore.db
 442260 16 Jul 14:31 data/graph.db/neostore.nodestore.db
   8192 16 Jul 14:31 data/graph.db/neostore.schemastore.db
   8190 16 Jul 14:31 data/graph.db/neostore.labeltokenstore.db
   8190 16 Jul 14:31 data/graph.db/neostore.relationshiptypestore.db
   8175 16 Jul 14:31 data/graph.db/neostore.relationshipgroupstore.db

Set heap from 8 to 16G, depending on the RAM size of the machine. Also configure the young generation in conf/neo4j-wrapper.conf.

wrapper.java.initmemory=8000
wrapper.java.maxmemory=8000
wrapper.java.additional=-Xmn2G


That’s mostly it, config-wise. If you are concurrency heavy, you could also set the webserver threads in conf/neo4j-server.properties.

# cpu * 2
org.neo4j.server.webserver.maxthreads=24

QUERY TUNING


If these previous factors are taken care of, it’s now time to dig into query tuning. A lot of query tuning is simply prefixing your statements with EXPLAIN to see what Cypher would do and using PROFILE to retrieve the real execution data as well:

For example, let’s look at this query, which has the PROFILE prefix:

PROFILE
MATCH(g:Genre {name:"Action"})<-[:GENRE]-(m:Movie)<-[:ACTS_IN]-(a)
WHERE a.name =~ "A.*"
RETURN distinct a.name;


The result of this query is shown below in the visual query plan tool available in the Neo4j browser.

A Screenshot of a Query Plan for More Effective Query Tuning


While the visual query plan is nice in the Neo4j browser, the one in Neo4j-shell is easier to compare and it also has more raw numbers.

Operator Est.Rows Rows DbHits Identifiers Other

Total database accesses: 72310

Distinct

2048

860

2636

a.name

a.name

Filter(0)

2155

1318

41532

anon[32], anon[52], a, g, m

a.name ~= /{ AUTOSTRING1}/

Expand(All)(0)

2874

20766

23224

anon[32], anon[52], a, g, m

(m)←[:ACTS_IN]-(a)

Filter(1)

390

2458

2458

anon[32], g, m

m:Movie

Expand(All)(1)

390

2458

2459

anon[32], g, m

(g)←[:GENRE]-(m)

NodeUniqueIndexSeek

1

1

1

g

:Genre(name)



Query Tuning Tip #1: Use Indexes and Constraints for Nodes You Look Up by Properties

Check – with either schema or :schema – that there is an index in place for non-unique values and a constraint for unique values, and make sure – with EXPLAIN – that the index is used in your query.

CREATE INDEX ON :Movie(title);
CREATE INDEX ON :Person(name);
CREATE CONSTRAINT ON (g:Genre) ASSERT g.name IS UNIQUE;


Even for range queries (pre-Neo4j 2.3), it might be better to turn them into an IN query to leverage an index.

// if :Movie(released) is indexed, this query for the nineties will *not use* an index:
MATCH (m:Movie) WHERE m.released >= 1990 and m.released < 2000
RETURN count(*);

CREATE INDEX ON :Movie(released);

// but this will
MATCH (m:Movie) WHERE m.released IN range(1990,1999)  RETURN count(*);

// same for OR queries
MATCH (m:Movie) WHERE m.released = 1990 OR m.released = 1991 OR ...


Query Tuning Tip #2: Patterns with Bound Nodes are Optimized

If you have a pattern (node)-[:REL]→(node) where both nodes on either side are already bound, Cypher will optimize the match by taking the node-degree (number of relationships) into account when checking for the connection, starting on the smaller side and also caching internally.

So, for example, (actor)-[:ACTS_IN]->(movie) – if both actor and movie are known – turns into that described Expand(Into) operation.

If one side is not known, then it is a normal Expand(All) operation.

Query Tuning Tip #3: Enforce Index Lookups for Both Sides of a Path

Make sure that if nodes on both sides of a longer path can be found in an index, and are only a few hits of a larger total count, to add USING INDEX for both sides. In many cases, that makes a big difference. It doesn't help if the path explodes in the middle and a simple left-to-right traversal with property checks would touch fewer paths.

PROFILE
MATCH (a:Person {name:"Tom Hanks"})-[:ACTS_IN]->()<-[:ACTS_IN]-(b:Person {name:"Meg Ryan"})
RETURN count(*);

Operator Est.Rows Rows DbHits Identifiers Other

Total database accesses: 765

EagerAggregation

0

1

0

count(*)

Filter

0

3

531

anon[36], anon[49], anon[51], a, b

a:Person AND a.name == { AUTOSTRING0}) AND NOT(anon[36] == anon[51]

Expand(All)(0)

3

177

204

anon[36], anon[49], anon[51], a, b

()←[:ACTS_IN]-(a)

Expand(All)(1)

2

27

28

anon[49], anon[51], b

(b)-[:ACTS_IN]→()

NodeIndexSeek

1

1

2

b

:Person(name)


If we add the second index-hint, we get 10x fewer database hits.

PROFILE
MATCH (a:Person {name:"Tom Hanks"})-[:ACTS_IN]->()<-[:ACTS_IN]-(b:Person {name:"Meg Ryan"})
USING INDEX a:Person(name) USING INDEX b:Person(name)
RETURN count(*);

Operator Est.Rows Rows DbHits Identifiers Other

Total database accesses: 68

EagerAggregation

0

1

0

count(*)

Filter

0

3

0

anon[36], anon[49], anon[51], a, b

NOT(anon[36] == anon[51])

NodeHashJoin

0

3

0

anon[36], anon[49], anon[51], a, b

anon[49]

Expand(All)(0)

2

27

28

anon[49], anon[51], b

(b)-[:ACTS_IN]→()

NodeIndexSeek(0)

1

1

2

b

:Person(name)

Expand(All)(1)

2

35

36

anon[36], anon[49], a

(a)-[:ACTS_IN]→()

NodeIndexSeek(1)

1

1

2

a

:Person(name)


Query Tuning Tip #4: Defer Property Access

Make sure to access properties only as the last operation – if possible – and on the smallest set of nodes and relationships. Massive property loading is more expensive than following relationships.

For example, this query:

PROFILE
MATCH (p:Person)-[:ACTS_IN]->(m:Movie)
RETURN p.name, count(*) as c
ORDER BY c DESC limit 10;

Operator Est.Rows Rows DbHits Identifiers Other

Total database accesses: 404525

Projection(0)

308

10

0

anon[48], anon[54], c, p.name

anon[48]; anon[54]

Top

308

10

0

anon[48], anon[54]

{ AUTOINT0};

EagerAggregation

308

44689

0

anon[48], anon[54]

anon[48]

Projection(1)

94700

94700

189400

anon[48], anon[17], m, p

p.name

Filter

94700

94700

94700

anon[17], m, p

p:Person

Expand(All)

94700

94700

107562

anon[17], m, p

(m)←[:ACTS_IN]-(p)

NodeByLabelScan

12862

12862

12863

m

:Movie


This query shown above accesses p.name for all people, totaling 400,000 database hits. Instead, you should aggregate on the node first, then order and paginate, and only in the very end should you access and return the property.

PROFILE
MATCH (p:Person)-[:ACTS_IN]->(m:Movie)
WITH p, count(*) as c
ORDER BY c DESC LIMIT 10
RETURN p.name, c;

This second query above only accesses p.name for the top ten actors, and before that, it groups them directly by the nodes, saving us about 200,000 database hits.

Operator Est.Rows Rows DbHits Identifiers Other

Total database accesses: 215145

Projection

308

10

20

c, p, p.name

p.name; c

Top

308

10

0

c, p

{ AUTOINT0}; c

EagerAggregation

308

44943

0

c, p

p

Filter

94700

94700

94700

anon[17], m, p

p:Person

Expand(All)

94700

94700

107562

anon[17], m, p

(m)←[:ACTS_IN]-(p)

NodeByLabelScan

12862

12862

12863

m

:Movie

But that query could even be optimized more, with....​

Query Tuning Tip #5: Fast Relationship Counting

There is an optimal implementation for single path-expressions, by directly reading the degree of a node. Personally, I always prefer this method over optional matches, exists or general where conditions: size((s)-[:REL]->()) ← uses get-degree which is a constant time operation (similarly without rel-type or direction).

PROFILE
MATCH (n:Person) WHERE EXISTS((n)-[:DIRECTED]->())
RETURN count(*);

Here the plan doesn’t count the nested db-hits in the expression, which it should. That’s why I included the runtime:

1 row 197 ms

Operator Est.Rows Rows DbHits Identifiers Other

Total database accesses: 106396

EagerAggregation

194

1

56216

count(*)

Filter

37634

6037

0

n

NestedPipeExpression(ExpandAllPipe(…​.))

NodeByLabelScan

50179

50179

50180

n


versus

PROFILE
MATCH (n:Person) WHERE size((n)-[:DIRECTED]->()) <> 0
RETURN count(*);

1 row 90 ms

Operator Est.Rows Rows DbHits Identifiers Other

Total database accesses: 150538

EagerAggregation

213

1

0

count(*)

Filter

45161

6037

100358

n

NOT(GetDegree(n,Some(DIRECTED),OUTGOING) == { AUTOINT0})

NodeByLabelScan

50179

50179

50180

n

:Person


You can also use that technique nicely for overview pages or inline aggregations:

PROFILE
MATCH (m:Movie)
RETURN m.title, size((m)<-[:ACTS_IN]-()) as actors, size((m)<-[:DIRECTED]-()) as directors
LIMIT 10;

+-------------------------------------------------------------+
| m.title                                | actors | directors |
+-------------------------------------------------------------+
| "Indiana Jones and the Temple of Doom" | 13     | 1         |
| "King Kong"                            | 1      | 1         |
| "Stolen Kisses"                        | 21     | 1         |
| "One Flew Over The Cuckoo's Nest"      | 24     | 1         |
| "Ziemia obiecana"                      | 17     | 1         |
| "Scoop"                                | 21     | 1         |
| "Fire"                                 | 0      | 1         |
| "Dial M For Murder"                    | 5      | 1         |
| "Ed Wood"                              | 21     | 1         |
| "Requiem"                              | 11     | 1         |
+-------------------------------------------------------------+
10 rows
13 ms

Operator Est.Rows Rows DbHits Identifiers Other

Total database accesses: 71

Projection

12862

10

60

actors, directors,

m.title; GetDegree(m,Some(ACTS_IN),INCOMING);

m, m.title

GetDegree(m,Some(DIRECTED),INCOMING)

Limit

12862

10

0

m

{ AUTOINT0}

NodeByLabelScan

12862

10

11

m

:Movie


Our query from the previous section would look like this:

PROFILE
MATCH (p:Person)
WITH p, sum(size((p)-[:ACTS_IN]->())) as c
ORDER BY c DESC LIMIT 10
RETURN p.name, c;

This query shaves off another 50,000 database hits. Not bad.

Operator Est.Rows Rows DbHits Identifiers Other

Total database accesses: 150558

Projection

224

10

20

c, p, p.name

p.name; c

Top

224

10

0

c, p

{ AUTOINT0}; c

EagerAggregation

224

50179

100358

c, p

p

NodeByLabelScan

50179

50179

50180

p

:Person


Note to self: Optimized Cypher looks more like Lisp.

Bonus Query Tuning Tip: Reduce Cardinality of Work in Progress

When following longer paths, you’ll encounter duplicates. If you’re not interested in all the possible paths – but just distinct information from stages of the path – make sure that you eagerly eliminate duplicates, so that later matches don’t have to be executed many multiple times.

This reduction of the cardinality can be done using either WITH DISTINCT or WITH aggregation (which automatically de-duplicates).

So, for instance, for this query of "Movies that Tom Hanks' colleagues acted in":

PROFILE
MATCH (p:Person {name:"Tom Hanks"})-[:ACTS_IN]->(m1)<-[:ACTS_IN]-(coActor)-[:ACTS_IN]->(m2)
RETURN distinct m2.title;

This query has 10,272 db-hits and touches 3,020 total paths.

Operator Est.Rows Rows DbHits Identifiers Other

Total database accesses: 10272

Distinct

4

2021

6040

m2.title

m2.title

Filter(0)

4

3020

0

anon[36], anon[53], anon[75], coActor, m1, m2, p

(NOT(anon[53] == anon[75]) AND NOT(anon[36] == anon[75]))

Expand(All)(0)

4

3388

3756

anon[36], anon[53], anon[75], coActor, m1, m2, p

(coActor)-[:ACTS_IN]→(m2)

Filter(1)

3

368

0

anon[36], anon[53], coActor, m1, p

NOT(anon[36] == anon[53])

Expand(All)(1)

3

403

438

anon[36], anon[53], coActor, m1, p

(m1)←[:ACTS_IN]-(coActor)

Expand(All)(2)

2

35

36

anon[36], m1, p

(p)-[:ACTS_IN]→(m1)

NodeIndexSeek

1

1

2

p

:Person(name)


The first-degree neighborhood is unique, since in this dataset there is only at most one :ACTS_IN relationship between an actor and a movie. So, the first duplicated nodes appear at the second degree, which we can eliminate like this:

PROFILE
MATCH (p:Person {name:"Tom Hanks"})-[:ACTS_IN]->(m1)<-[:ACTS_IN]-(coActor)
WITH distinct coActor
MATCH (coActor)-[:ACTS_IN]->(m2)
RETURN distinct m2.title;

This query tuning technique reduces the number of paths to match for the last step to 2,906. In other use cases with more duplicates, the impact is much bigger.

Operator Est.Rows Rows DbHits Identifiers Other

Total database accesses: 9529

Distinct(0)

4

2031

5812

m2.title

m2.title

Expand(All)(0)

4

2906

3241

anon[113], coActor, m2

(coActor)-[:ACTS_IN]→(m2)

Distinct(1)

3

335

0

coActor

coActor

Filter

3

368

0

anon[36], anon[53], coActor, m1, p

NOT(anon[36] == anon[53])

Expand(All)(1)

3

403

438

anon[36], anon[53], coActor, m1, p

(m1)←[:ACTS_IN]-(coActor)

Expand(All)(2)

2

35

36

anon[36], m1, p

(p)-[:ACTS_IN]→(m1)

NodeIndexSeek

1

1

2

p

:Person(name)


Of course we would apply our Minimize Property Access tip here too:

PROFILE
MATCH (p:Person {name:"Tom Hanks"})-[:ACTS_IN]->(m1)<-[:ACTS_IN]-(coActor)
WITH distinct coActor
MATCH (coActor)-[:ACTS_IN]->(m2)
WITH distinct m2
RETURN m2.title;

Operator Est.Rows Rows DbHits Identifiers Other

Total database accesses: 7791

Projection

4

2037

4074

m2, m2.title

m2.title

Distinct(0)

4

2037

0

m2

m2

Expand(All)(0)

4

2906

3241

anon[113], coActor, m2

(coActor)-[:ACTS_IN]→(m2)

Distinct(1)

3

335

0

coActor

coActor

Filter

3

368

0

anon[36], anon[53], coActor, m1, p

NOT(anon[36] == anon[53])

Expand(All)(1)

3

403

438

anon[36], anon[53], coActor, m1, p

(m1)←[:ACTS_IN]-(coActor)

Expand(All)(2)

2

35

36

anon[36], m1, p

(p)-[:ACTS_IN]→(m1)

NodeIndexSeek

1

1

2

p

:Person(name)


We still need the distinct m2 at the end, as the co-actors can have played in the same movies, and we don’t want duplicate results.

This query has 7,791 db-hits and touches 2,906 paths in total.

If you are also interested in the frequency (e.g., for scoring), you can compute them along with an aggregation instead of distinctly. In the end, You just multiply the path count per co-actor with the number of occurrences per movie.

MATCH (p:Person {name:"Tom Hanks"})-[:ACTS_IN]->(m1)<-[:ACTS_IN]-(coActor)
WITH coActor, count(*) as freq
MATCH (coActor)-[:ACTS_IN]->(m2)
RETURN m2.title, freq * count(*) as occurrence;

Conclusion


The best way to start with query tuning is to take the slowest queries, PROFILE them and optimize them using these tips.

If you need help, you can always reach out to us on Stack Overflow, our Google Group or our public Slack channel.

If you are part of a project that is adopting Neo4j or putting it into production, make sure to get some expert help to ensure you’re successful. Note: If you do ask for help, please provide enough information for others to be able to help you. Explain your graph model, share your queries, their profile output and – best of all – a dataset to run them on.


Need more tips on how to effectively use Neo4j? Register for our online training class, Neo4j in Production, and learn how to master the world’s leading graph database.

Sign Me Up

 

Keywords:  


About the Author

Michael Hunger, Developer Relations

Michael Hunger Image

Michael Hunger has been passionate about software development for a very long time. For the last few years he has been working with Neo Technology on the open source Neo4j graph database filling many roles. As caretaker of the Neo4j community and ecosystem he especially loves to work with graph-related projects, users and contributors. As a developer Michael enjoys many aspects of programming languages, learning new things every day, participating in exciting and ambitious open source projects and contributing and writing software related books and articles.


2 Comments

Ryan says:

Great article! This kind of information is super helpful to devs like me who are writing and tuning Cypher every day. There were a bunch of suggestions I didn’t know about. Thanks!

ALEXANDROS P. says:

Great article! But I had performed my own test for Query Tuning Tip #5: Fast Relationship Counting
and I have different results. plan of query with size doesn’t count the nested db-hits and it is slower.

————————————————————————
Building neo4j_0 1.0-SNAPSHOT
————————————————————————

— exec-maven-plugin:1.2.1:exec (default-cli) @ neo4j_0 —
========================
Query with WHERE:
========================
All nodes degree : 19973 Total DbHits: 21973
========================
========================
Query with SIZE:
========================
All nodes degree : 19973 Total DbHits: 3000
========================
Average runtime
Total Time with WHERE: 3845064863ns
Total Time with SIZE: 5291941883ns

the code:

package tests;

import static BenchMark.BenchmarksCypher.getDbHits;
import java.io.File;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import org.neo4j.graphdb.ExecutionPlanDescription;
import org.neo4j.graphdb.GraphDatabaseService;
import org.neo4j.graphdb.Node;
import org.neo4j.graphdb.ResourceIterator;
import org.neo4j.graphdb.Result;
import org.neo4j.graphdb.Transaction;
import org.neo4j.graphdb.factory.GraphDatabaseFactory;

/**
*
* @author Alex
*/
public class testCypherQueries {

/**
* @param args the command line arguments
*/
public static void registerShutdownHook(final GraphDatabaseService graphDb) {
Runtime.getRuntime().addShutdownHook(new Thread() {
@Override
public void run() {
graphDb.shutdown();
}
});
}

public static HashMap getDegree1(GraphDatabaseService graphDB) {

int degree = 0, dbHits = 0;
Result result = null;
String query;
Node node;
Iterator nodes;
HashMap ret = new HashMap();
ResourceIterator nodesIterator = graphDB.getAllNodes().iterator();
while (nodesIterator.hasNext()) {
node = nodesIterator.next();
query = “PROFILE MATCH (a)-[]-() WHERE id(a) = ” + node.getId() + ” RETURN count(*) as degree;”;
result = graphDB.execute(query);
Long x = (Long) result.columnAs(“degree”).next();
degree += x.intValue();
ExecutionPlanDescription plan = result.getExecutionPlanDescription();
dbHits += getDbHits(plan, 0);
}

ret.put(degree, dbHits);
return ret;
}

public static HashMap getDegree2(GraphDatabaseService graphDB) {

int degree = 0, dbHits = 0;
Result result = null;
String query;
Node node;
Iterator nodes;
HashMap ret = new HashMap();
ResourceIterator nodesIterator = graphDB.getAllNodes().iterator();
while (nodesIterator.hasNext()) {
node = nodesIterator.next();
query = “PROFILE MATCH (a) WHERE id(a) = ” + node.getId() + ” AND size((a)-[]-()) 0 RETURN size((a)-[]-()) as degree;”;
result = graphDB.execute(query);
Long x = (Long) result.columnAs(“degree”).next();
degree += x.intValue();
ExecutionPlanDescription plan = result.getExecutionPlanDescription();
dbHits += getDbHits(plan, 0);
}

ret.put(degree, dbHits);
return ret;
}

public static void printMap(HashMap map) {
System.out.println(“========================”);
for (Map.Entry entry : map.entrySet()) {
System.out.println(“All nodes degree : ” + entry.getKey() + ” Total DbHits: ” + entry.getValue());
}
System.out.println(“========================”);
}

public static void main(String[] args) {
GraphDatabaseService graphDB;
long startTimef, entTime, totalTime1, totalTime2, avg1 = 0, avg2 = 0;
File datafile = new File(“C:\\Users\\Alex\\Documents\\Neo4j\\GeneratedGraphs\\RandomGraph1000_0.02”);

graphDB = new GraphDatabaseFactory().newEmbeddedDatabaseBuilder(datafile)
.loadPropertiesFromFile(“C:\\neo4j-community-2.3.2\\conf\\” + “neo4j.conf”).newGraphDatabase();

registerShutdownHook(graphDB);

try (Transaction tx = graphDB.beginTx()) {

for (int i = 0; i < 10; i++) {

startTimef = System.nanoTime();
HashMap degree1 = getDegree1(graphDB);
entTime = System.nanoTime();
totalTime1 = (entTime – startTimef);

startTimef = System.nanoTime();
HashMap degree2 = getDegree2(graphDB);
entTime = System.nanoTime();
totalTime2 = (entTime – startTimef);

System.out.println(“Query with WHERE:\t”);
printMap(degree1);
System.out.println(“Query with SIZE:\t”);
printMap(degree2);
avg1 += totalTime1;
avg2 += totalTime2;

}

System.out.println(“Average runtime”);
System.out.println(“Total Time with WHERE:\t” + avg1 / 10L + “ns”);
System.out.println(“Total Time with SIZE:\t” + avg2 / 10L + “ns”);
}
}
}

Leave a Reply

Your email address will not be published. Required fields are marked *

Subscribe

Upcoming Event

 


From the CEO

Emil's Blog


Have a Graph Question?

Stackoverflow
Slack
Contact Us

Share your Graph Story?

Email us: content@neotechnology.com


Popular Graph Topics