Neo4j CPU 100% for a simple variable-length path traversal

Neo4j Graph Platform Cypher
1

Hi,

I upgraded my Neo4j docker container from 4.2.4 to 4.4.2 today,
and I'm facing some huge performance issues when querying a simple variable length path traversal.

A query like the following:

MATCH (t:Tree)-[:HAS_CHILD_BLOB|HAS_CHILD_TREE*]->(b:Blob)
WHERE t.hash = '51ac215688f1b1713e9a7992292e75f86defd5c9'
RETURN count(b)

This query takes around 200 seconds to execute, for a count of 300 blobs btw ...
I don't recall having so much performance issues with the previous version.

When I ssh on my instance, I see the CPU spike at 100% all the time when the query is being executed.
Is that expected ?

Here is the profile of the query:

plan
plan507×818 61 KB

Cypher version: CYPHER 4.4, planner: COST, runtime: INTERPRETED. 353729021 total db hits in 201249 ms.

All I want to do is to reach all the children of this Tree, recursively.

And for information, the maximum path length is ... 11:

Capture d’écran de 2021-12-23 20-42-49
Capture d’écran de 2021-12-23 20-42-49792×306 28.3 KB

Any tips ?
Something wrong with my query, or the planner here ?

I just tried to run the same query (on a different data set), on AuraDB:

Capture d’écran de 2021-12-23 20-58-34
Capture d’écran de 2021-12-23 20-58-341502×517 45.9 KB

Started streaming 1 records in less than 1 ms and completed after 94 ms.

It works fine.
But the query planner is different:

plan(2)
plan(2)221×915 46.4 KB

What's happening on my docker instance ??

Thanks !

3

@Wenzel

if you preface the query with

CYPHER runtime=pipelined

do you get better results?

4

Hi @dana_canzano and thank you very much for your answer !

As I'm running the community edition where the performance issue is triggered, I can't test your query tuning trick, since it's limited on the Enterprise edition only.

5

This looks like some buggy planner behavior. Can you let us know how many :Tree nodes are in your db, how many :Blob nodes, how many :HAS_CHILD_BLOB relationships, and how many :HAS_CHILD_TREE relationships?

In the meantime, see if this performs better.

MATCH (t:Tree)-[:HAS_CHILD_BLOB|HAS_CHILD_TREE*]->(b)
WHERE t.hash = '51ac215688f1b1713e9a7992292e75f86defd5c9'
WITH t, b, 1 as ignored
WHERE b:Blob
RETURN count(b)

We want to see a plan like the second one you posted, that only expands from your starting t node and doesn't perform any label scans or cartesian product operations.

6

Hi @andrew_bowman ,

I realized that I didn't followed up on this topic, and now I'm facing the same problem 2 years later:

When I take that simple query on Neo4j 5.21:

MATCH (t:Tree)-[:HAS_CHILD_BLOB|HAS_CHILD_TREE*]->(b:Blob)
WHERE t.hash = $root_hash
RETURN count(b)

I still get a query plan including a cartesian product.

And with the solution you proposed (WITH t, b, 1 as ignored), the product is gone and the query executes as expected.

Do you still think it might be a bug in the planner ?

Like in the Github issue I described, I wasn't facing these issues with Neo4J 5.15.

For example, the same query on 5.15 gives me this plan:

plan(4)
plan(4)331×879 43.2 KB

How can I get a reliable execution plan, excluding a cartesian product, to traverse all the blobs given a specific Tree ? :thinking:

7

This is going beyond just query tuning, and requires a check from our engineers.

So you have a workaround at the moment, but for determining if the planner's choice is a result of a bug, or otherwise something our engineers can change in the planner code, the github issue is actually the better place for getting an answer for that, as you'll have the attention of our Cypher engineers.

1 Like