neo4j - Improving a Cypher query

Question

I have a performance critical application which has to match multiple nodes to another node based on regex matching. My current query is as follows:

MATCH (person: Person {name: 'Mark'})
WITH person
UNWIND person.match_list AS match
MATCH (pet: Animal) 
WHERE pet.name_regex =~ match
MERGE (person)-[:OWNS_PET]->(pet) 
RETURN pet

However, this query runs VERY slow (around 500ms on my workstation). The graph contains around 500K nodes, and around 10K will match the regex.

I'm wondering whether there is a more efficient way to re-write this query to work the same but provide a performance increase.

EDIT:

When I run this query on several Persons multithreaded I get a TransientError exception

neo4j.exceptions.TransientError: ForsetiClient[3] can't acquire ExclusiveLock{owner=ForsetiClient[14]} on NODE(1889), because holders of that lock are waiting for ForsetiClient[3].

EDIT 2:

Person:name is unique and indexed

Animal:name_regex is not indexed

So does Mark have 10k :Animal nodes which match the regex, or are you attempting to loop this query over multiple people in your input? Please describe your approach in full, and also let us know if you have an index on :Person(name) — InverseFalcon
@InverseFalcon, hi and thank you for your fast aid. The query will be run against many Persons, each one may have or may not have pets (depends on his match rules). The query aims to connect each person and his matched pets, according to a regular expression (if a pet's name, which is saved in regex form, match any rule within the Person's match list) — Lewis Bushman
@InverseFalcon, also another issue is that when I run this query on multiple users simultaneously (multithreaded) I get a TransientError — Lewis Bushman
@LewisBushman the error you've got is due to a locking issue, it's a dead lock exception. — logisima

Tezra Tezra · Accepted Answer · 2018-10-15T20:31:33

First, I would start by simplifying your query as much as possible. The way you are doing it now creates a lot of wasted effort after a match has been found

MATCH (person: Person {name: 'Mark'}), (pet: Animal)
WHERE ANY(match in person.match_list WHERE pet.name_regex =~ match)
MERGE (person)-[:OWNS_PET]->(pet) 
RETURN pet

This will make it so that only 1 merge is attempted if there are multiple matches, and once one match is found, the rest won't be attempted on the same pet. This also allows Cypher to optimize to the best of it's ability on your data.

To improve the cypher further, you will need to optimize your data. For example, regex match is expensive (requires a node+string scan), if the match statements can be largely reused between people, it would be better to break them out into a node, and then connect to those so that the work of one regex match can be reused everywhere it's repeated.

neo4j - Improving a Cypher query

1 Answers