AQL查询非常慢(约20秒)

Question

执行以下查询大约需要20秒：

FOR p IN PATHS(locations, connections, "outbound", { maxLength: 1 }) FILTER p.source._key == "26094" RETURN p.vertices[*].name

我相信这是一个简单的查询(数据库没有那么大)，它应该执行得相当快。。。我一定做错了什么。。。以下是查询结果：

==> [object ArangoQueryCursor - count: 286, hasMore: false]

locations(顶点)集合有23753个文档，connections(边)集合有123414个文档。

我也尝试过用_id进行过滤，但性能有些相同。

我能做些什么来获得更好的表现吗

以下是查询的.explain()报告：

{ 
"plan" : { 
"nodes" : [ 
{ 
"type" : "SingletonNode", 
"dependencies" : [ ], 
"id" : 1, 
"estimatedCost" : 1, 
"estimatedNrItems" : 1 
}, 
{ 
"type" : "CalculationNode", 
"dependencies" : [ 
1 
], 
"id" : 2, 
"estimatedCost" : 2, 
"estimatedNrItems" : 1, 
"expression" : { 
"type" : "function call", 
"name" : "PATHS", 
"subNodes" : [ 
{ 
"type" : "array", 
"subNodes" : [ 
{ 
"type" : "collection", 
"name" : "locations" 
}, 
{ 
"type" : "collection", 
"name" : "connections" 
}, 
{ 
"type" : "value", 
"value" : "outbound" 
}, 
{ 
"type" : "object", 
"subNodes" : [ 
{ 
"type" : "object element", 
"name" : "maxLength", 
"subNodes" : [ 
{ 
"type" : "value", 
"value" : 1 
} 
] 
} 
] 
} 
] 
} 
] 
}, 
"outVariable" : { 
"id" : 2, 
"name" : "2" 
}, 
"canThrow" : true 
}, 
{ 
"type" : "EnumerateListNode", 
"dependencies" : [ 
2 
], 
"id" : 3, 
"estimatedCost" : 102, 
"estimatedNrItems" : 100, 
"inVariable" : { 
"id" : 2, 
"name" : "2" 
}, 
"outVariable" : { 
"id" : 0, 
"name" : "p" 
} 
}, 
{ 
"type" : "CalculationNode", 
"dependencies" : [ 
3 
], 
"id" : 4, 
"estimatedCost" : 202, 
"estimatedNrItems" : 100, 
"expression" : { 
"type" : "compare ==", 
"subNodes" : [ 
{ 
"type" : "attribute access", 
"name" : "_key", 
"subNodes" : [ 
{ 
"type" : "attribute access", 
"name" : "source", 
"subNodes" : [ 
{ 
"type" : "reference", 
"name" : "p", 
"id" : 0 
} 
] 
} 
] 
}, 
{ 
"type" : "value", 
"value" : "26094" 
} 
] 
}, 
"outVariable" : { 
"id" : 3, 
"name" : "3" 
}, 
"canThrow" : false 
}, 
{ 
"type" : "FilterNode", 
"dependencies" : [ 
4 
], 
"id" : 5, 
"estimatedCost" : 302, 
"estimatedNrItems" : 100, 
"inVariable" : { 
"id" : 3, 
"name" : "3" 
} 
}, 
{ 
"type" : "CalculationNode", 
"dependencies" : [ 
5 
], 
"id" : 6, 
"estimatedCost" : 402, 
"estimatedNrItems" : 100, 
"expression" : { 
"type" : "expand", 
"subNodes" : [ 
{ 
"type" : "iterator", 
"subNodes" : [ 
{ 
"type" : "variable", 
"name" : "1_", 
"id" : 1 
}, 
{ 
"type" : "attribute access", 
"name" : "vertices", 
"subNodes" : [ 
{ 
"type" : "reference", 
"name" : "p", 
"id" : 0 
} 
] 
} 
] 
}, 
{ 
"type" : "attribute access", 
"name" : "name", 
"subNodes" : [ 
{ 
"type" : "reference", 
"name" : "1_", 
"id" : 1 
} 
] 
} 
] 
}, 
"outVariable" : { 
"id" : 4, 
"name" : "4" 
}, 
"canThrow" : false 
}, 
{ 
"type" : "ReturnNode", 
"dependencies" : [ 
6 
], 
"id" : 7, 
"estimatedCost" : 502, 
"estimatedNrItems" : 100, 
"inVariable" : { 
"id" : 4, 
"name" : "4" 
} 
} 
], 
"rules" : [ 
"move-calculations-up", 
"move-filters-up", 
"move-calculations-up-2", 
"move-filters-up-2" 
], 
"collections" : [ 
{ 
"name" : "connections", 
"type" : "read" 
}, 
{ 
"name" : "locations", 
"type" : "read" 
} 
], 
"variables" : [ 
{ 
"id" : 0, 
"name" : "p" 
}, 
{ 
"id" : 1, 
"name" : "1_" 
}, 
{ 
"id" : 2, 
"name" : "2" 
}, 
{ 
"id" : 3, 
"name" : "3" 
}, 
{ 
"id" : 4, 
"name" : "4" 
} 
], 
"estimatedCost" : 502, 
"estimatedNrItems" : 100 
}, 
"warnings" : [ ], 
"stats" : { 
"rulesExecuted" : 21, 
"rulesSkipped" : 0, 
"plansCreated" : 1 
} 
}

Answer 1

PATHS()将构建图的所有路径，然后使用_key属性上的FILTER对结果进行后过滤。这可能会在过滤掉所有不匹配之前，首先(为所有路径)创建一个巨大的结果集。

如果所需要的只是在深度1上找到连接的顶点，我认为这样做会更有效：

• 使用TRAVERSAL:进行查询

  这更有效，因为它将构建图中的所有路径，但仅构建从指定起始顶点开始的路径：

  FOR p IN TRAVERSAL(locations, connections, "1", "outbound", { minDepth: 1, maxDepth: 1, paths: true }) 
  RETURN p.path.vertices[*].name
  

• 使用NEIGHBORS:查询直接邻居

  这可能稍微更有效，即使因为它将构建较小的中间结果。此外，它不会返回起始顶点(26094)，而是返回所有直接连接到它的顶点：

  FOR p IN NEIGHBORS(locations, connections, "26094", "outbound") 
  RETURN p.vertex.name
  

• 直接查询边(不使用图形函数)

  最后，您可以直接查询边集合。同样，这不会返回起始顶点(26094)，而是直接连接到它的所有顶点：

  FOR edge IN connections
  FILTER edge._from == "locations/26094"
  FOR vertex IN locations
  FILTER vertex._id == edge._to
  RETURN vertex.name