Please keep the following things in mind:
I tried two same queries just with different relation symbolic names. It increase db hits quantity by 30%.
Database schema:
CALL db.schema.visualization()
What you thing, how it possible?
First query
Performance: total db hits 80049 in 85 ms
PROFILE
MATCH (preferences:UserPreferences {userId: $userId})
<-[:HAS_PREFERENCES]-(user:UserDetails {id: $userId})
-[:HAS_ACTIVITY]->(activity:UserSuggestionActivity {userId: $userId})
OPTIONAL MATCH (activity)-[:LIKED|ADDED]->(likedBook)
OPTIONAL MATCH (likedBook)<-[:LIKED|ADDED]-(otherActivity)-[:LIKED|ADDED]->(rec:Book {lang: $lang})
WHERE (NOT EXISTS((rec)--(activity))
AND EXISTS((preferences)-[:PREFERRED]->()<--(rec))
AND NOT EXISTS((preferences)-[:AVOIDED]->()<--(rec)))
WITH rec, count(DISTINCT otherActivity) AS activityCount
ORDER BY activityCount DESC
LIMIT $limit
MATCH (rec)-[:BELONGS_TO]->(g:Genre)
RETURN rec.title AS title, COLLECT(g) AS genres
Second query
Performance: total 65511 total db hits in 41 ms
PROFILE
MATCH (preferences:UserPreferences {userId: $userId})
<-[:HAS_PREFERENCES]-(user:UserDetails {id: $userId})
-[:HAS_ACTIVITY]->(activity:UserSuggestionActivity {userId: $userId})
OPTIONAL MATCH (activity)-[:LIKED|ADDED]->(likedBook)
OPTIONAL MATCH (likedBook)<-[:LIKED|ADDED]-(otherActivity)-[:LIKED|ADDED]->(rec:Book {lang: $lang})
WHERE (NOT EXISTS((rec)--(activity))
AND EXISTS((preferences)-[:PREFERRED]->()<--(rec))
AND NOT EXISTS((preferences)-[:AVOIDED]->()<--(rec)))
WITH rec, count(DISTINCT otherActivity) AS activityCount
ORDER BY activityCount DESC
LIMIT $limit
MATCH (rec)-[rel:BELONGS_TO]->(g:Genre) // just added name `rel`
RETURN rec.title AS title, COLLECT(g) AS genres
Here is detailed plans of queries:
First query
Planner COST
Runtime SLOTTED
Runtime version 5.18
+----------------------+----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| Operator | Id | Details | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Ordered by |
+----------------------+----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| +ProduceResults | 0 | title, genres | 0 | 2 | 6 | 984 | 0/0 | |
| | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| +EagerAggregation | 1 | cache[rec.title] AS title, COLLECT(g) AS genres | 0 | 2 | 0 | 1056 | 0/0 | |
| | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| +Filter | 2 | g:Genre | 0 | 3 | 3 | | 0/0 | |
| | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+ |
| +Expand(All) | 3 | (rec)-[anon_12:BELONGS_TO]->(g) | 0 | 3 | 9 | | 0/0 | |
| | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+ |
| +CacheProperties | 4 | cache[rec.title] | 0 | 3 | 2 | | 0/0 | |
| | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+ |
| +Top | 5 | activityCount DESC LIMIT $limit | 0 | 3 | 0 | 400 | 0/0 | activityCount DESC |
| | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| +EagerAggregation | 6 | rec, count(DISTINCT otherActivity) AS activityCount | 0 | 3 | 0 | 1440 | 0/0 | |
| | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| +Apply | 7 | | 0 | 82 | 0 | | 0/0 | |
| |\ +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | +Optional | 8 | user, preferences, anon_0, anon_2, anon_1, likedBook, activity | 0 | 82 | 0 | | 0/0 | |
| | | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | +AntiSemiApply | 9 | | 0 | 38 | 0 | | 0/0 | |
| | |\ +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | | +Expand(Into) | 10 | (rec)-[anon_5]-(activity) | 0 | 0 | 6311 | 904 | 0/0 | |
| | | | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | | +Argument | 11 | anon_0, anon_1, activity, likedBook, anon_2, rec, preferences, user | 0 | 1554 | 0 | | 0/0 | |
| | | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | +AntiSemiApply | 12 | | 0 | 1554 | 0 | | 0/0 | |
| | |\ +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | | +Filter | 13 | NOT anon_11 = anon_9 | 0 | 0 | 0 | | 0/0 | |
| | | | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | | +Expand(Into) | 14 | (anon_10)<-[anon_11]-(rec) | 0 | 62 | 8703 | 1000 | 0/0 | |
| | | | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | | +Expand(All) | 15 | (preferences)-[anon_9:AVOIDED]->(anon_10) | 0 | 1616 | 6464 | | 0/0 | |
| | | | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | | +Argument | 16 | anon_0, anon_1, activity, likedBook, anon_2, rec, preferences, user | 0 | 1616 | 0 | | 0/0 | |
| | | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | +SemiApply | 17 | | 0 | 1616 | 0 | | 0/0 | |
| | |\ +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | | +Filter | 18 | NOT anon_8 = anon_6 | 0 | 0 | 0 | | 0/0 | |
| | | | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | | +Expand(Into) | 19 | (anon_7)<-[anon_8]-(rec) | 0 | 1616 | 25563 | 1000 | 0/0 | |
| | | | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | | +Expand(All) | 20 | (preferences)-[anon_6:PREFERRED]->(anon_7) | 0 | 4594 | 18376 | | 0/0 | |
| | | | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | | +Argument | 21 | anon_0, anon_1, activity, likedBook, anon_2, rec, preferences, user | 0 | 4594 | 0 | | 0/0 | |
| | | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | +Filter | 22 | NOT anon_4 = anon_3 AND rec.lang = $lang AND rec:Book | 0 | 4594 | 9188 | | 0/0 | |
| | | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | +Expand(All) | 23 | (otherActivity)-[anon_4:LIKED|ADDED]->(rec) | 0 | 4681 | 5058 | | 0/0 | |
| | | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | +Expand(All) | 24 | (likedBook)<-[anon_3:LIKED|ADDED]-(otherActivity) | 0 | 87 | 287 | | 0/0 | |
| | | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | +Argument | 25 | anon_0, anon_1, activity, likedBook, anon_2, preferences, user | 0 | 63 | 0 | | 0/0 | |
| | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| +OptionalExpand(All) | 26 | (activity)-[anon_2:LIKED|ADDED]->(likedBook) | 0 | 63 | 67 | | 0/0 | |
| | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| +Filter | 27 | preferences.userId = $userId AND preferences:UserPreferences | 0 | 1 | 2 | | 0/0 | |
| | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| +Expand(All) | 28 | (user)-[anon_0:HAS_PREFERENCES]->(preferences) | 0 | 1 | 3 | | 0/0 | |
| | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| +Filter | 29 | activity.userId = $userId AND activity:UserSuggestionActivity | 0 | 1 | 2 | | 0/0 | |
| | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| +Expand(All) | 30 | (user)-[anon_1:HAS_ACTIVITY]->(activity) | 0 | 1 | 3 | | 0/0 | |
| | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| +NodeIndexSeek | 31 | RANGE INDEX user:UserDetails(id) WHERE id = $userId | 0 | 1 | 2 | | 0/0 | |
+----------------------+----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
Total database accesses: 80049, total allocated memory: 2568
Second query
Planner COST
Runtime SLOTTED
Runtime version 5.18
+----------------------+----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| Operator | Id | Details | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Ordered by |
+----------------------+----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| +ProduceResults | 0 | title, genres | 0 | 2 | 6 | 984 | 0/0 | |
| | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| +EagerAggregation | 1 | cache[rec.title] AS title, COLLECT(g) AS genres | 0 | 2 | 0 | 1056 | 0/0 | |
| | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| +Filter | 2 | g:Genre | 0 | 3 | 3 | | 0/0 | |
| | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+ |
| +Expand(All) | 3 | (rec)-[rel:BELONGS_TO]->(g) | 0 | 3 | 9 | | 0/0 | |
| | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+ |
| +CacheProperties | 4 | cache[rec.title] | 0 | 3 | 2 | | 0/0 | |
| | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+ |
| +Top | 5 | activityCount DESC LIMIT $limit | 0 | 3 | 0 | 400 | 0/0 | activityCount DESC |
| | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| +EagerAggregation | 6 | rec, count(DISTINCT otherActivity) AS activityCount | 0 | 3 | 0 | 1440 | 0/0 | |
| | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| +Apply | 7 | | 0 | 82 | 0 | | 0/0 | |
| |\ +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | +Optional | 8 | user, preferences, anon_0, anon_2, anon_1, likedBook, activity | 0 | 82 | 0 | | 0/0 | |
| | | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | +AntiSemiApply | 9 | | 0 | 38 | 0 | | 0/0 | |
| | |\ +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | | +Filter | 10 | NOT anon_11 = anon_9 | 0 | 0 | 0 | | 0/0 | |
| | | | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | | +Expand(Into) | 11 | (anon_10)<-[anon_11]-(rec) | 0 | 0 | 209 | 904 | 0/0 | |
| | | | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | | +Expand(All) | 12 | (preferences)-[anon_9:AVOIDED]->(anon_10) | 0 | 38 | 152 | | 0/0 | |
| | | | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | | +Argument | 13 | anon_0, anon_1, activity, likedBook, anon_2, rec, preferences, user | 0 | 38 | 0 | | 0/0 | |
| | | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | +AntiSemiApply | 14 | | 0 | 38 | 0 | | 0/0 | |
| | |\ +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | | +Expand(Into) | 15 | (rec)-[anon_5]-(activity) | 0 | 0 | 6559 | 904 | 0/0 | |
| | | | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | | +Argument | 16 | anon_0, anon_1, activity, likedBook, anon_2, rec, preferences, user | 0 | 1616 | 0 | | 0/0 | |
| | | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | +SemiApply | 17 | | 0 | 1616 | 0 | | 0/0 | |
| | |\ +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | | +Filter | 18 | NOT anon_8 = anon_6 | 0 | 0 | 0 | | 0/0 | |
| | | | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | | +Expand(Into) | 19 | (anon_7)<-[anon_8]-(rec) | 0 | 1616 | 25563 | 1000 | 0/0 | |
| | | | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | | +Expand(All) | 20 | (preferences)-[anon_6:PREFERRED]->(anon_7) | 0 | 4594 | 18376 | | 0/0 | |
| | | | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | | +Argument | 21 | anon_0, anon_1, activity, likedBook, anon_2, rec, preferences, user | 0 | 4594 | 0 | | 0/0 | |
| | | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | +Filter | 22 | NOT anon_4 = anon_3 AND rec.lang = $lang AND rec:Book | 0 | 4594 | 9188 | | 0/0 | |
| | | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | +Expand(All) | 23 | (otherActivity)-[anon_4:LIKED|ADDED]->(rec) | 0 | 4681 | 5058 | | 0/0 | |
| | | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | +Expand(All) | 24 | (likedBook)<-[anon_3:LIKED|ADDED]-(otherActivity) | 0 | 87 | 287 | | 0/0 | |
| | | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| | +Argument | 25 | anon_0, anon_1, activity, likedBook, anon_2, preferences, user | 0 | 63 | 0 | | 0/0 | |
| | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| +OptionalExpand(All) | 26 | (activity)-[anon_2:LIKED|ADDED]->(likedBook) | 0 | 63 | 67 | | 0/0 | |
| | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| +Filter | 27 | preferences.userId = $userId AND preferences:UserPreferences | 0 | 1 | 2 | | 0/0 | |
| | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| +Expand(All) | 28 | (user)-[anon_0:HAS_PREFERENCES]->(preferences) | 0 | 1 | 3 | | 0/0 | |
| | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| +Filter | 29 | activity.userId = $userId AND activity:UserSuggestionActivity | 0 | 1 | 2 | | 0/0 | |
| | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| +Expand(All) | 30 | (user)-[anon_1:HAS_ACTIVITY]->(activity) | 0 | 1 | 3 | | 0/0 | |
| | +----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
| +NodeIndexSeek | 31 | RANGE INDEX user:UserDetails(id) WHERE id = $userId | 0 | 1 | 2 | | 0/0 | |
+----------------------+----+---------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+--------------------+
Total database accesses: 65491, total allocated memory: 2568