I have this query “Get Recommended/Similar Products” on both RedisGraph and ArangoDB, and RedisGraph is showing significant slow execution time in comparison to ArangoDB. Although RedisGraph benchmarks show that it should have a better performance than other graph databases.
Ref: Benchmarking RedisGraph 1.0 | Redis
The query fetches what other products users who have purchased this specific product (id: 123) have also purchased, while filtering out the current user’s (id: 321) purchases, and also while ordering the result by the products that occurred the most.
Here is the RedisGraph query I used:
MATCH (:product {id: 123})<-[:purchased]-(:user)-[r:purchased]->(p:product)
WHERE NOT (:user {id: 321})-[:purchased]->(p)
WITH p, COUNT(r) as occurrence
WHERE occurrence > 9
RETURN p.id as product_id, occurrence
ORDER BY occurrence DESC
LIMIT 5
It had an execution time of 6177.225 ms.
While the ArangoDB query looks like this:
LET userPurchases = (FOR purchase IN OUTBOUND 'users/321' purchases RETURN purchase._id)
FOR product,purchase IN 2..2 ANY 'products/123' purchases
FILTER purchase._to NOT IN userPurchases
COLLECT product_id = product._key WITH COUNT INTO occurrence
FILTER occurrence > 9
SORT occurrence DESC
LIMIT 5
RETURN {product_id, occurrence}
And it had an execution time of only 33.256 ms.
Note that both (user) and (product) nodes are indexed on “id” property.
Is there a way to optimize my Redisgraph query any further to make it match/exceed ArangoDB’s performance?
More information regarding the schemas of both databases:
Would you mind sharing the execution-plan generated for this query ?
In addition which version of RedisGraph are you using ?
Hello, I am using RedisGraph v20412.
And here is the execution plan:
- “Results”
- " Limit"
- " Sort"
- " Project"
- " Filter"
- " Aggregate"
- " Anti Semi Apply"
- " Conditional Traverse | (anon_2:user)-[r:purchased]->(p:product)"
- " Conditional Traverse | (anon_0:product)->(anon_2:user)"
- " Index Scan | (anon_0:product)"
- " Filter"
- " Conditional Traverse | (p:product)->(anon_3:user)"
- " Argument"
I believe you can improve your query time by performing the following change:
MATCH (u:user {id: 321})
WITH u
MATCH (:product {id: 123})<-[:purchased]-(:user)-[:purchased]->(p:product)
WHERE NOT (u)-[:purchased]->(p)
WITH p, COUNT(p) as occurrence
WHERE occurrence > 9
RETURN p.id as product_id, occurrence
ORDER BY occurrence DESC
LIMIT 5
Hello, thanks for you suggestion, I can see that it significantly increased the performance from 6295.218080 milliseconds to 864.024231 milliseconds.
However, compared to ArangoDB’s 33 milliseconds , it’s still slower.
I tried also removing the filtering part of both queries and compare:
RedisGraph:
MATCH (:product {id: 123})<-[:purchased]-(:user)-[r:purchased]->(p:product)
WITH p, COUNT(r) as occurrence
WHERE occurrence > 9
RETURN p.id as product_id, occurrence
ORDER BY occurrence DESC
LIMIT 5
Execution time: 100 milliseconds.
ArangoDB:
FOR product,purchase IN 2..2 ANY 'products/123' purchases
COLLECT product_id = product._key WITH COUNT INTO occurrence
FILTER occurrence > 9
SORT occurrence DESC
LIMIT 5
RETURN {product_id, occurrence}
Execution time: 15 milliseconds.
And as you can see, there’s still a huge difference… Any suggestions?
Which version of RedisGraph are you using?
also can you please share the output from GRAPH.PROFILE for this query?
RedisGraoh v2.0.4.
Here are the results of running GRAPH,PROFILE on both queries:
>> GRAPH.PROFILE "products" "MATCH (:product {id: 123})<-[:purchased]-(:user)-[r:purchased]->(p:product) WITH p, COUNT(r) as occurrence WHERE occurrence > 9 RETURN p.id as product_id, occurrence ORDER BY occurrence DESC LIMIT 5 "
1) "Results | Records produced: 5, Execution time: 0.002299 ms"
2) " Limit | Records produced: 5, Execution time: 0.001408 ms"
3) " Sort | Records produced: 5, Execution time: 0.044173 ms"
4) " Project | Records produced: 199, Execution time: 0.370976 ms"
5) " Filter | Records produced: 199, Execution time: 1.143295 ms"
6) " Aggregate | Records produced: 5835, Execution time: 61.558675 ms"
7) " Conditional Traverse | (anon_2:user)-[r:purchased]->(p:product) | Records produced: 19342, Execution time: 48.798157 ms"
8) " Conditional Traverse | (anon_2:user)->(anon_2:user) | Records produced: 1899, Execution time: 4.135292 ms"
9) " Index Scan | (anon_0:product) | Records produced: 1, Execution time: 0.200022 ms"
>> GRAPH.PROFILE "products" "MATCH (u:user {id: 321}) WITH u MATCH (:product {id: 123})<-[:purchased]-(:user)-[r:purchased]->(p:product) WHERE NOT (u)-[:purchased]->(p) WITH p, COUNT(r) as occurrence WHERE occurrence > 9 RETURN p.id as product_id, occurrence ORDER BY occurrence DESC LIMIT 5"
1) "Results | Records produced: 5, Execution time: 0.001442 ms"
2) " Limit | Records produced: 5, Execution time: 0.001090 ms"
3) " Sort | Records produced: 5, Execution time: 0.029856 ms"
4) " Project | Records produced: 195, Execution time: 0.146955 ms"
5) " Filter | Records produced: 195, Execution time: 0.804495 ms"
6) " Aggregate | Records produced: 5829, Execution time: 66.170424 ms"
7) " Anti Semi Apply | Records produced: 17352, Execution time: 15.535811 ms"
8) " Conditional Traverse | (anon_2:user)-[r:purchased]->(p:product) | Records produced: 19342, Execution time: 41.071432 ms"
9) " Conditional Traverse | (anon_2:user)->(anon_2:user) | Records produced: 1899, Execution time: 2.249151 ms"
10) " Index Scan | (anon_0:product) | Records produced: 1, Execution time: 0.088107 ms"
11) " Project | Records produced: 1, Execution time: 0.002567 ms"
12) " Index Scan | (u:user) | Records produced: 1, Execution time: 0.114068 ms"
13) " Expand Into | (u)->(p:product) | Records produced: 1990, Execution time: 705.989461 ms"
14) " Argument | Records produced: 19342, Execution time: 5.848560 ms"