Hi
I’m working with a fairly large Neo4j dataset containing about ~2.3 million OrderLines nodes and roughly ~.5 millionComplianceRecord nodes stored for a tenant. The below cypher takes approximately 12-14 seconds to return response. I would like to know if it is possible make this query return response faster without reducing the dataset? Any suggestions for improving the performance of this query would be greatly appreciated.
Cypher:
MATCH (orderline:`OrderLine` {
tenantId: 'tenant1', type:'OrderLine'
}) USING INDEX orderline:`OrderLine`(tenantId, plannedShipDate)
WHERE (orderline.plannedShipDate < '2025-06-20T18:30:00.001Z' AND orderline.plannedShipDate >= '2022-12-31T18:30:00.000Z')
WITH orderline
OPTIONAL MATCH (orderline)-[:`ORDERLINE_PCFCOMPLIANCERECORD`]->(compliancerecord_pcfComplianceRecord:`ComplianceRecord`)
RETURN sum((compliancerecord_pcfComplianceRecord.score * orderline.quantity)) AS TotalEmissionsByAll
PROFILE Output:
{
"results": [
{
"columns": [
"TotalEmissionsByAll"
],
"data": [
{
"row": [
5.1915847641664444E10
],
"meta": [
null
]
}
],
"plan": {
"root": {
"operatorType": "ProduceResults@neo4j",
"GlobalMemory": 168,
"planner-impl": "IDP",
"Memory": 0,
"runtime": "INTERPRETED",
"runtime-impl": "INTERPRETED",
"version": "CYPHER 4.4",
"DbHits": 0,
"Details": "TotalEmissionsByAll",
"planner-version": "4.4",
"runtime-version": "4.4",
"PageCacheMisses": 0,
"EstimatedRows": 1.0,
"planner": "COST",
"Rows": 1,
"PageCacheHits": 0,
"identifiers": [
"TotalEmissionsByAll"
],
"children": [
{
"operatorType": "EagerAggregation@neo4j",
"Details": "sum(compliancerecord_pcfComplianceRecord.score * cache[orderline.quantity]) AS TotalEmissionsByAll",
"Memory": 64,
"PageCacheMisses": 0,
"EstimatedRows": 1.0,
"DbHits": 1149154,
"Rows": 1,
"PageCacheHits": 0,
"identifiers": [
"TotalEmissionsByAll"
],
"children": [
{
"operatorType": "OptionalExpand(All)@neo4j",
"Details": "(orderline)-[anon_0:ORDERLINE_PCFCOMPLIANCERECORD]->(compliancerecord_pcfComplianceRecord) WHERE compliancerecord_pcfComplianceRecord:ComplianceRecord",
"PageCacheMisses": 0,
"EstimatedRows": 8565.241875,
"DbHits": 3438667,
"Rows": 1152073,
"PageCacheHits": 0,
"identifiers": [
"orderline",
"anon_0",
"compliancerecord_pcfComplianceRecord"
],
"children": [
{
"operatorType": "Filter@neo4j",
"Details": "orderline.type = $autostring_1",
"PageCacheMisses": 0,
"EstimatedRows": 8565.241875,
"DbHits": 1140359,
"Rows": 1140359,
"PageCacheHits": 0,
"identifiers": [
"orderline"
],
"children": [
{
"operatorType": "CacheProperties@neo4j",
"Details": "cache[orderline.quantity]",
"PageCacheMisses": 0,
"EstimatedRows": 1259.7910761687024,
"DbHits": 1140359,
"Rows": 1140359,
"PageCacheHits": 0,
"identifiers": [
"orderline"
],
"children": [
{
"operatorType": "NodeIndexSeek@neo4j",
"Details": "BTREE INDEX orderline:OrderLine(tenantId, plannedShipDate) WHERE tenantId = $autostring_0 AND plannedShipDate >= $autostring_3 AND plannedShipDate < $autostring_2",
"PageCacheMisses": 0,
"EstimatedRows": 1259.7910761687024,
"DbHits": 1140360,
"Rows": 1140359,
"PageCacheHits": 0,
"identifiers": [
"orderline"
],
"children": []
}
]
}
]
}
]
}
]
}
]
}
}
}
],
"errors": []
}