Neo4j - count very slow

Question

I am running this query (bisac_code is uniquely indexed). Execution time is more than 2.5 minutes. 52 main codes are selected from almost 4000 in total. The total number of wokas is very large, 19 million nodes.

Are there any possibilities to make it run faster?

neo4j-sh (?)$ MATCH (b:Bisac)-[r:INCLUDED_IN]-(w:Woka)
> WHERE (b.bisac_code =~ '.*000000') 
> RETURN b.bisac_code as bisac_code, count(w) as wokas_count
> ORDER BY b.bisac_code
> ;

+---------------------------+
| bisac_code  | wokas_count |
+---------------------------+
| "ANT000000" | 13865       |
| "ARC000000" | 32905       |
| "ART000000" | 79600       |
| "BIB000000" | 2043        |
| "BIO000000" | 256082      |
| "BUS000000" | 226173      |
| "CGN000000" | 16424       |
| "CKB000000" | 26410       |
| "COM000000" | 44922       |
| "CRA000000" | 18720       |
| "DES000000" | 2713        |
| "DRA000000" | 62610       |
| "EDU000000" | 228182      |
| "FAM000000" | 42951       |
| "FIC000000" | 474004      |
| "FOR000000" | 41999       |
| "GAM000000" | 8803        |
| "GAR000000" | 37844       |
| "HEA000000" | 36939       |
| "HIS000000" | 3908869     |
| "HOM000000" | 5123        |
| "HUM000000" | 29270       |
| "JNF000000" | 40396       |
| "JUV000000" | 200144      |
| "LAN000000" | 89059       |
| "LAW000000" | 153138      |
| "LCO000000" | 1528237     |
| "LIT000000" | 89611       |
| "MAT000000" | 58134       |
| "MED000000" | 80268       |
| "MUS000000" | 75997       |
| "NAT000000" | 35991       |
| "NON000000" | 107513      |
| "OCC000000" | 42134       |
| "PER000000" | 26989       |
| "PET000000" | 4980        |
| "PHI000000" | 72069       |
| "PHO000000" | 8546        |
| "POE000000" | 104609      |
| "POL000000" | 309153      |
| "PSY000000" | 55710       |
| "REF000000" | 96477       |
| "REL000000" | 133619      |
| "SCI000000" | 86017       |
| "SEL000000" | 40901       |
| "SOC000000" | 292713      |
| "SPO000000" | 172284      |
| "STU000000" | 10508       |
| "TEC000000" | 77459       |
| "TRA000000" | 9093        |
| "TRU000000" | 12041       |
| "TRV000000" | 27706       |
+---------------------------+
52 rows
198310 ms

And the response time is not consistent. After a while drops to less than half of a minute.

52 rows
31207 ms

Answer 1

In Neo4j 2.3 there will be index support for prefix LIKE searches but probably not for postfix ones.

There are two ways of making @user2194039's solution faster:

Use path expression to count the Woka per Bisac:

MATCH (b:Bisac) WHERE (b.bisac_code =~ '.*000000')
WITH b, size((b)-[:INCLUDED_IN]->()) as wokas_count
RETURN b.bisac_code as bisac_code, wokas_count
ORDER BY b.bisac_code

Mark the Bisac's with that pattern with a label

MATCH (b:Bisac) WHERE (b.bisac_code =~ '.*000000') SET b:Main;

MATCH (b:Main:Bisac)
WITH b, size((b)-[:INCLUDED_IN]->()) as wokas_count
RETURN b.bisac_code as bisac_code, wokas_count
ORDER BY b.bisac_code;

Answer 2

The slow speed is caused by your regular expression pattern matching (=~ ). Although your bisac_code is indexed, the regex match causes the index to be ineffective. The index only works when you are matching full bisac_code values.

Cypher does include some string manipulation facilities that might let you get by without using a regex =~, but I doubt it would make any difference, because the index will still be useless.

I might suggest considering if you can further categorize your bisac_codes so that you do not need to do a pattern match. Maybe an extra indexed property that somehow denotes those codes that end in 000000?

If you do not want to add properties, you may try matching only the Bisacs first, and then including the Wokas. Something like this:

MATCH (b:Bisac) WHERE (b.bisac_code =~ '.*000000')
WITH b
MATCH (b)-[r:INCLUDED_IN]-(w:Woka)
RETURN b.bisac_code as bisac_code, count(w) as wokas_count
ORDER BY b.bisac_code

This may help Cypher stick to the 4000 Bisac nodes while doing the pattern match, before getting involved with all 19 million Woka nodes, but I am not sure if this will make a material difference. Even slogging through 4000 nodes (effectively without an index) is a slow process.

---

Hash Tables in Database Indexing

The reason that your index is ineffective for regex pattern matching is that Neo4j likely uses a hash table for indexing properties. This is common of many databases. Wikipedia has an article here.

The basics though are that the index is not storing all of the properties that you want to search through. It is storing values that represent the properties you want to search through, and the representation is only valid for the whole property. If you are searching for only a part of the property value, the hashes stored in the index are useless, and the database must search through the properties the old-fashioned way -- one by one.

---

Edit re: your edit

The improvement in response time after running this query multiple times is certainly due to caching. Neo4j is remembering that you access the Bisac nodes and bisac_code properties frequently, and is keeping them in memory. This makes future queries faster because the values do not need to be read off disk.

However, eventually, those nodes a properties will likely be dropped from the cache, as Neo4j finds you manipulating different nodes, which it will cache instead. There are only so many nodes Neo4j can cache before running out of memory, so it picks the most recent and/or frequently used data.