Slowness in execution of Cypher query when we use multiple relationship and inner queries

[vrangana]

• neo4j version, desktop version, browser version : 4.4.35
• what kind of API / driver do you use : neo4j-java-driver (4.4.12)

Following is a relationship based Cypher query executed to extract data at each relationship level. When are executing this Cypher directly via the Neo4j browser. Query is executed to get data at different Node level (Order -> Order -> Organization -> Catalog<3 levels> ) and we extract data from each level for our reporting. When Neo4j runs this cypher which is multi level based the query takes around 25secs to 30secs to get a response. But if we reduced the relationship to Order -> Order -> Organization -> Catalog <1 level>, then the query returns within 8secs. I am confused whether this a Cypher query problem (or) are there any performance improvements which can be done for this cypher query execution to speed up the response time. Any kind of inputs to improve the cypher execution is highly appreciated. Thanks in advance

Note: I have also attached the explain plan for analysis.

MATCH (orderline:`OrderLine` {
  tenantId: '853357c4-28f3-4f69-b95a-7ee38363e798', type: 'OrderLine'
})
WITH orderline SKIP 0
LIMIT 51 CALL {
  WITH orderline
  MATCH (orderline)-[:`RELATES_TO` {fieldName: 'order'
}]->(order:`Order` {
  tenantId: '853357c4-28f3-4f69-b95a-7ee38363e798'
})
WITH order CALL {
  WITH order
  MATCH (order)-[:`RELATES_TO` {fieldName: 'buyer'
}]->(organization:`Organization` {
  tenantId: '853357c4-28f3-4f69-b95a-7ee38363e798'
})
WITH organization CALL {
  WITH organization
  MATCH (organization)-[:`RELATES_TO` {fieldName: 'category1'
}]->(catalog:`Catalog` {
  tenantId: '853357c4-28f3-4f69-b95a-7ee38363e798'
})
WITH catalog
WITH collect(catalog{.*, _id: id(catalog)})[0..6] AS collectionAlias, count(catalog) AS countAlias
RETURN {
  dataList: collectionAlias, totalCount: countAlias
} AS Organization_Catalog_category1__info} CALL {
  WITH organization
  MATCH (organization)-[:`RELATES_TO` {fieldName: 'category2'
}]->(catalog:`Catalog` {
  tenantId: '853357c4-28f3-4f69-b95a-7ee38363e798'
})
WITH catalog
WITH collect(catalog{.*, _id: id(catalog)})[0..6] AS collectionAlias, count(catalog) AS countAlias
RETURN {
  dataList: collectionAlias, totalCount: countAlias
} AS Organization_Catalog_category2__info} CALL {
  WITH organization
  MATCH (organization)-[:`RELATES_TO` {fieldName: 'category3'
}]->(catalog:`Catalog` {
  tenantId: '853357c4-28f3-4f69-b95a-7ee38363e798'
})
WITH catalog
WITH collect(catalog{.*, _id: id(catalog)})[0..6] AS collectionAlias, count(catalog) AS countAlias
RETURN {
  dataList: collectionAlias, totalCount: countAlias
} AS Organization_Catalog_category3__info}
WITH collect(organization{.*, _id: id(organization), category1: Organization_Catalog_category1__info, category2: Organization_Catalog_category2__info, category3: Organization_Catalog_category3__info})[0..6] AS collectionAlias, count(organization) AS countAlias
RETURN {
  dataList: collectionAlias, totalCount: countAlias
} AS Order_Organization_buyer__info}
WITH collect(order{.*, _id: id(order), buyer: Order_Organization_buyer__info})[0..6] AS collectionAlias, count(order) AS countAlias
RETURN {
  dataList: collectionAlias, totalCount: countAlias
} AS OrderLine_Order_order__info}
RETURN orderline{.*, _id: id(orderline), order: OrderLine_Order_order__info}

• screenshot of PROFILE or EXPLAIN with boxes expanded (lower right corner)

{
	"results": [
		{
			"columns": [
				"orderline"
			],
			"data": [],
			"plan": {
				"root": {
					"operatorType": "ProduceResults@neo4j",
					"planner-impl": "IDP",
					"Details": "orderline",
					"planner-version": "4.4",
					"runtime-version": "4.4",
					"runtime": "INTERPRETED",
					"runtime-impl": "INTERPRETED",
					"version": "CYPHER 4.4",
					"EstimatedRows": 51,
					"planner": "COST",
					"identifiers": [
						"orderline",
						"countAlias",
						"OrderLine_Order_order__info",
						"anon_28",
						"collectionAlias",
						"anon_29"
					],
					"children": [
						{
							"operatorType": "Projection@neo4j",
							"Details": "orderline{_id: id(orderline), order: OrderLine_Order_order__info, .*} AS orderline",
							"EstimatedRows": 51,
							"identifiers": [
								"orderline",
								"countAlias",
								"OrderLine_Order_order__info",
								"anon_28",
								"collectionAlias",
								"anon_29"
							],
							"children": [
								{
									"operatorType": "Projection@neo4j",
									"Details": "{dataList: collectionAlias, totalCount: countAlias} AS OrderLine_Order_order__info",
									"EstimatedRows": 51,
									"identifiers": [
										"orderline",
										"countAlias",
										"OrderLine_Order_order__info",
										"anon_28",
										"collectionAlias",
										"anon_29"
									],
									"children": [
										{
											"operatorType": "Projection@neo4j",
											"Details": "anon_29[0..6] AS collectionAlias, anon_28 AS countAlias",
											"EstimatedRows": 51,
											"identifiers": [
												"orderline",
												"countAlias",
												"anon_28",
												"collectionAlias",
												"anon_29"
											],
											"children": [
												{
													"operatorType": "Apply@neo4j",
													"EstimatedRows": 51,
													"identifiers": [
														"orderline",
														"anon_28",
														"anon_29"
													],
													"children": [
														{
															"operatorType": "Skip@neo4j",
															"Details": "$autoint_2",
															"EstimatedRows": 51,
															"identifiers": [
																"orderline"
															],
															"children": [
																{
																	"operatorType": "Limit@neo4j",
																	"Details": "51 + $autoint_2",
																	"EstimatedRows": 52,
																	"identifiers": [
																		"orderline"
																	],
																	"children": [
																		{
																			"operatorType": "NodeIndexSeek@neo4j",
																			"Details": "BTREE INDEX orderline:OrderLine(tenantId, type) WHERE tenantId = $autostring_0 AND type = $autostring_1",
																			"EstimatedRows": 52.00000000000001,
																			"identifiers": [
																				"orderline"
																			],
																			"children": []
																		}
																	]
																}
															]
														},
														{
															"operatorType": "EagerAggregation@neo4j",
															"Details": "count(order) AS anon_28, collect(order{_id: id(order), buyer: Order_Organization_buyer__info, .*}) AS anon_29",
															"EstimatedRows": 51,
															"identifiers": [
																"anon_28",
																"anon_29"
															],
															"children": [
																{
																	"operatorType": "Projection@neo4j",
																	"Details": "{dataList: collectionAlias, totalCount: countAlias} AS Order_Organization_buyer__info",
																	"EstimatedRows": 0.051043379947513935,
																	"identifiers": [
																		"orderline",
																		"countAlias",
																		"anon_0",
																		"anon_26",
																		"collectionAlias",
																		"anon_27",
																		"Order_Organization_buyer__info",
																		"order"
																	],
																	"children": [
																		{
																			"operatorType": "Projection@neo4j",
																			"Details": "anon_27[0..6] AS collectionAlias, anon_26 AS countAlias",
																			"EstimatedRows": 0.051043379947513935,
																			"identifiers": [
																				"orderline",
																				"countAlias",
																				"anon_0",
																				"anon_26",
																				"collectionAlias",
																				"anon_27",
																				"order"
																			],
																			"children": [
																				{
																					"operatorType": "Apply@neo4j",
																					"EstimatedRows": 0.051043379947513935,
																					"identifiers": [
																						"orderline",
																						"anon_0",
																						"anon_26",
																						"anon_27",
																						"order"
																					],
																					"children": [
																						{
																							"operatorType": "Filter@neo4j",
																							"Details": "order:Order AND anon_0.fieldName = \"order\" AND order.tenantId = \"853357c4-28f3-4f69-b95a-7ee38363e798\"",
																							"EstimatedRows": 0.051043379947513935,
																							"identifiers": [
																								"orderline",
																								"anon_0",
																								"order"
																							],
																							"children": [
																								{
																									"operatorType": "Expand(All)@neo4j",
																									"Details": "(orderline)-[anon_0:RELATES_TO]->(order)",
																									"EstimatedRows": 208.5736852658071,
																									"identifiers": [
																										"orderline",
																										"anon_0",
																										"order"
																									],
																									"children": [
																										{
																											"operatorType": "Argument@neo4j",
																											"Details": "orderline",
																											"EstimatedRows": 51,
																											"identifiers": [
																												"orderline"
																											],
																											"children": []
																										}
																									]
																								}
																							]
																						},
																						{
																							"operatorType": "EagerAggregation@neo4j",
																							"Details": "count(organization) AS anon_26, collect(organization{_id: id(organization), category1: Organization_Catalog_category1__info, category2: Organization_Catalog_category2__info, category3: Organization_Catalog_category3__info, .*}) AS anon_27",
																							"EstimatedRows": 0.051043379947513935,
																							"identifiers": [
																								"anon_26",
																								"anon_27"
																							],
																							"children": [
																								{
																									"operatorType": "Projection@neo4j",
																									"Details": "{dataList: collectionAlias, totalCount: countAlias} AS Organization_Catalog_category3__info",
																									"EstimatedRows": 0.00007163826404597566,
																									"identifiers": [
																										"countAlias",
																										"Organization_Catalog_category2__info",
																										"anon_20",
																										"Organization_Catalog_category3__info",
																										"anon_22",
																										"anon_21",
																										"anon_24",
																										"anon_23",
																										"anon_1",
																										"organization",
																										"anon_25",
																										"collectionAlias",
																										"Organization_Catalog_category1__info",
																										"order"
																									],
																									"children": [
																										{
																											"operatorType": "Projection@neo4j",
																											"Details": "anon_25[0..6] AS collectionAlias, anon_24 AS countAlias",
																											"EstimatedRows": 0.00007163826404597566,
																											"identifiers": [
																												"countAlias",
																												"Organization_Catalog_category2__info",
																												"anon_20",
																												"anon_22",
																												"anon_21",
																												"anon_24",
																												"anon_23",
																												"anon_1",
																												"organization",
																												"anon_25",
																												"collectionAlias",
																												"Organization_Catalog_category1__info",
																												"order"
																											],
																											"children": [
																												{
																													"operatorType": "Apply@neo4j",
																													"EstimatedRows": 0.00007163826404597566,
																													"identifiers": [
																														"countAlias",
																														"Organization_Catalog_category2__info",
																														"anon_20",
																														"anon_22",
																														"anon_21",
																														"anon_24",
																														"anon_23",
																														"anon_1",
																														"organization",
																														"anon_25",
																														"collectionAlias",
																														"Organization_Catalog_category1__info",
																														"order"
																													],
																													"children": [
																														{
																															"operatorType": "Projection@neo4j",
																															"Details": "{dataList: collectionAlias, totalCount: countAlias} AS Organization_Catalog_category2__info",
																															"EstimatedRows": 0.00007163826404597566,
																															"identifiers": [
																																"anon_20",
																																"anon_22",
																																"countAlias",
																																"anon_21",
																																"anon_23",
																																"anon_1",
																																"organization",
																																"collectionAlias",
																																"Organization_Catalog_category2__info",
																																"Organization_Catalog_category1__info",
																																"order"
																															],
																															"children": [
																																{
																																	"operatorType": "Projection@neo4j",
																																	"Details": "anon_23[0..6] AS collectionAlias, anon_22 AS countAlias",
																																	"EstimatedRows": 0.00007163826404597566,
																																	"identifiers": [
																																		"anon_20",
																																		"anon_22",
																																		"countAlias",
																																		"anon_21",
																																		"anon_23",
																																		"anon_1",
																																		"organization",
																																		"collectionAlias",
																																		"Organization_Catalog_category1__info",
																																		"order"
																																	],
																																	"children": [
																																		{
																																			"operatorType": "Apply@neo4j",
																																			"EstimatedRows": 0.00007163826404597566,
																																			"identifiers": [
																																				"anon_20",
																																				"anon_22",
																																				"countAlias",
																																				"anon_21",
																																				"anon_23",
																																				"anon_1",
																																				"organization",
																																				"collectionAlias",
																																				"Organization_Catalog_category1__info",
																																				"order"
																																			],
																																			"children": [
																																				{
																																					"operatorType": "Projection@neo4j",
																																					"Details": "{dataList: collectionAlias, totalCount: countAlias} AS Organization_Catalog_category1__info",
																																					"EstimatedRows": 0.00007163826404597566,
																																					"identifiers": [
																																						"anon_20",
																																						"countAlias",
																																						"anon_21",
																																						"anon_1",
																																						"organization",
																																						"collectionAlias",
																																						"Organization_Catalog_category1__info",
																																						"order"
																																					],
																																					"children": [
																																						{
																																							"operatorType": "Projection@neo4j",
																																							"Details": "anon_21[0..6] AS collectionAlias, anon_20 AS countAlias",
																																							"EstimatedRows": 0.00007163826404597566,
																																							"identifiers": [
																																								"anon_20",
																																								"countAlias",
																																								"anon_21",
																																								"anon_1",
																																								"organization",
																																								"collectionAlias",
																																								"order"
																																							],
																																							"children": [
																																								{
																																									"operatorType": "Apply@neo4j",
																																									"EstimatedRows": 0.00007163826404597566,
																																									"identifiers": [
																																										"anon_20",
																																										"anon_21",
																																										"anon_1",
																																										"organization",
																																										"order"
																																									],
																																									"children": [
																																										{
																																											"operatorType": "Filter@neo4j",
																																											"Details": "organization:Organization AND anon_1.fieldName = \"buyer\" AND organization.tenantId = \"853357c4-28f3-4f69-b95a-7ee38363e798\"",
																																											"EstimatedRows": 0.00007163826404597566,
																																											"identifiers": [
																																												"anon_1",
																																												"organization",
																																												"order"
																																											],
																																											"children": [
																																												{
																																													"operatorType": "Expand(All)@neo4j",
																																													"Details": "(order)-[anon_1:RELATES_TO]->(organization)",
																																													"EstimatedRows": 0.1584807429686049,
																																													"identifiers": [
																																														"anon_1",
																																														"organization",
																																														"order"
																																													],
																																													"children": [
																																														{
																																															"operatorType": "Argument@neo4j",
																																															"Details": "order",
																																															"EstimatedRows": 0.051043379947513935,
																																															"identifiers": [
																																																"order"
																																															],
																																															"children": []
																																														}
																																													]
																																												}
																																											]
																																										},
																																										{
																																											"operatorType": "EagerAggregation@neo4j",
																																											"Details": "count(catalog) AS anon_20, collect(catalog{_id: id(catalog), .*}) AS anon_21",
																																											"EstimatedRows": 0.00007163826404597566,
																																											"identifiers": [
																																												"anon_20",
																																												"anon_21"
																																											],
																																											"children": [
																																												{
																																													"operatorType": "Filter@neo4j",
																																													"Details": "catalog:Catalog AND anon_2.fieldName = \"category1\" AND catalog.tenantId = \"853357c4-28f3-4f69-b95a-7ee38363e798\"",
																																													"EstimatedRows": 1.9365575603626337e-7,
																																													"identifiers": [
																																														"catalog",
																																														"organization",
																																														"anon_2"
																																													],
																																													"children": [
																																														{
																																															"operatorType": "Expand(All)@neo4j",
																																															"Details": "(organization)-[anon_2:RELATES_TO]->(catalog)",
																																															"EstimatedRows": 0.0008481053373955613,
																																															"identifiers": [
																																																"catalog",
																																																"organization",
																																																"anon_2"
																																															],
																																															"children": [
																																																{
																																																	"operatorType": "Argument@neo4j",
																																																	"Details": "organization",
																																																	"EstimatedRows": 0.00007163826404597566,
																																																	"identifiers": [
																																																		"organization"
																																																	],
																																																	"children": []
																																																}
																																															]
																																														}
																																													]
																																												}
																																											]
																																										}
																																									]
																																								}
																																							]
																																						}
																																					]
																																				},
																																				{
																																					"operatorType": "EagerAggregation@neo4j",
																																					"Details": "count(catalog) AS anon_22, collect(catalog{_id: id(catalog), .*}) AS anon_23",
																																					"EstimatedRows": 0.00007163826404597566,
																																					"identifiers": [
																																						"anon_22",
																																						"anon_23"
																																					],
																																					"children": [
																																						{
																																							"operatorType": "Filter@neo4j",
																																							"Details": "catalog:Catalog AND anon_3.fieldName = \"category2\" AND catalog.tenantId = \"853357c4-28f3-4f69-b95a-7ee38363e798\"",
																																							"EstimatedRows": 1.9365575603626337e-7,
																																							"identifiers": [
																																								"catalog",
																																								"organization",
																																								"anon_3"
																																							],
																																							"children": [
																																								{
																																									"operatorType": "Expand(All)@neo4j",
																																									"Details": "(organization)-[anon_3:RELATES_TO]->(catalog)",
																																									"EstimatedRows": 0.0008481053373955613,
																																									"identifiers": [
																																										"catalog",
																																										"organization",
																																										"anon_3"
																																									],
																																									"children": [
																																										{
																																											"operatorType": "Argument@neo4j",
																																											"Details": "organization",
																																											"EstimatedRows": 0.00007163826404597566,
																																											"identifiers": [
																																												"organization"
																																											],
																																											"children": []
																																										}
																																									]
																																								}
																																							]
																																						}
																																					]
																																				}
																																			]
																																		}
																																	]
																																}
																															]
																														},
																														{
																															"operatorType": "EagerAggregation@neo4j",
																															"Details": "count(catalog) AS anon_24, collect(catalog{_id: id(catalog), .*}) AS anon_25",
																															"EstimatedRows": 0.00007163826404597566,
																															"identifiers": [
																																"anon_24",
																																"anon_25"
																															],
																															"children": [
																																{
																																	"operatorType": "Filter@neo4j",
																																	"Details": "catalog:Catalog AND anon_4.fieldName = \"category3\" AND catalog.tenantId = \"853357c4-28f3-4f69-b95a-7ee38363e798\"",
																																	"EstimatedRows": 1.9365575603626337e-7,
																																	"identifiers": [
																																		"catalog",
																																		"organization",
																																		"anon_4"
																																	],
																																	"children": [
																																		{
																																			"operatorType": "Expand(All)@neo4j",
																																			"Details": "(organization)-[anon_4:RELATES_TO]->(catalog)",
																																			"EstimatedRows": 0.0008481053373955613,
																																			"identifiers": [
																																				"catalog",
																																				"organization",
																																				"anon_4"
																																			],
																																			"children": [
																																				{
																																					"operatorType": "Argument@neo4j",
																																					"Details": "organization",
																																					"EstimatedRows": 0.00007163826404597566,
																																					"identifiers": [
																																						"organization"
																																					],
																																					"children": []
																																				}
																																			]
																																		}
																																	]
																																}
																															]
																														}
																													]
																												}
																											]
																										}
																									]
																								}
																							]
																						}
																					]
																				}
																			]
																		}
																	]
																}
															]
														}
													]
												}
											]
										}
									]
								}
							]
						}
					]
				}
			}
		}
	],
	"errors": []
}

[vrangana]

This is the cypher query with relationship to Order -> Order -> Organization -> Catalog <1 level>, this runs within 8secs

MATCH (orderline:`OrderLine` { tenantId: '853357c4-28f3-4f69-b95a-7ee38363e798', type: 'OrderLine' }) WITH orderline SKIP 0 LIMIT 51 CALL { WITH orderline MATCH (orderline)-[:`RELATES_TO` {fieldName: 'order' }]->(order:`Order` { tenantId: '853357c4-28f3-4f69-b95a-7ee38363e798' }) WITH order CALL { WITH order MATCH (order)-[:`RELATES_TO` {fieldName: 'buyer' }]->(organization:`Organization` { tenantId: '853357c4-28f3-4f69-b95a-7ee38363e798' }) WITH organization CALL { WITH organization MATCH (organization)-[:`RELATES_TO` {fieldName: 'category1' }]->(catalog:`Catalog` { tenantId: '853357c4-28f3-4f69-b95a-7ee38363e798' }) WITH catalog WITH collect(catalog{.*, _id: id(catalog)})[0..6] AS collectionAlias, count(catalog) AS countAlias RETURN { dataList: collectionAlias, totalCount: countAlias } AS Organization_Catalog_category1__info} WITH collect(organization{.*, _id: id(organization), category1: Organization_Catalog_category1__info})[0..6] AS collectionAlias, count(organization) AS countAlias RETURN { dataList: collectionAlias, totalCount: countAlias } AS Order_Organization_buyer__info} WITH collect(order{.*, _id: id(order), buyer: Order_Organization_buyer__info})[0..6] AS collectionAlias, count(order) AS countAlias RETURN { dataList: collectionAlias, totalCount: countAlias } AS OrderLine_Order_order__info} RETURN orderline{.*, _id: id(orderline), order: OrderLine_Order_order__info}

[joshcornejo]

What about less nested calls?

MATCH (orderline:OrderLine {tenantId: '853357c4-28f3-4f69-b95a-7ee38363e798', type: 'OrderLine'})
      -[:RELATES_TO {fieldName: 'order'}]->(order:Order {tenantId:'853357c4-28f3-4f69-b95a-7ee38363e798'})
      -[:RELATES_TO {fieldName: 'buyer'}]->(organization:Organization {tenantId: '853357c4-28f3-4f69-b95a-7ee38363e798'})
      -[:RELATES_TO {fieldName: 'category1'}]->(catalog:Catalog {tenantId: '853357c4-28f3-4f69-b95a-7ee38363e798'})
    WITH collect(catalog{., _id: id(catalog)})[0..6] AS collectionAlias, count(catalog) AS countAlias
RETURN {
// etc etc

[vrangana]

@joshcornejo Thanks for the prompt response. Yes less nested calls will improve the performance. Reason why we used nested calls, so that we can extract properties from each level nodes (in the relationships). So at OrderLine, Order, Organization and Catalog nodes we need some properties to be used on the reporting. Thats why we add data using nested collect statements.

MATCH (orderline:`OrderLine` {
  tenantId: '853357c4-28f3-4f69-b95a-7ee38363e798', type: 'OrderLine'
})
WITH orderline SKIP 0
LIMIT 51 CALL {
  WITH orderline
  MATCH (orderline)-[:`RELATES_TO` {fieldName: 'order'
}]->(order:`Order` {
  tenantId: '853357c4-28f3-4f69-b95a-7ee38363e798'
})
WITH order CALL {
  WITH order
  MATCH (order)-[:`RELATES_TO` {fieldName: 'buyer'
}]->(organization:`Organization` {
  tenantId: '853357c4-28f3-4f69-b95a-7ee38363e798'
})
WITH organization CALL {
  WITH organization
  MATCH (organization)-[:`RELATES_TO` {fieldName: 'category1'
}]->(catalog:`Catalog` {
  tenantId: '853357c4-28f3-4f69-b95a-7ee38363e798'
})
WITH catalog
WITH collect(catalog{.*, _id: id(catalog)})[0..6] AS collectionAlias, count(catalog) AS countAlias
RETURN {
  dataList: collectionAlias, totalCount: countAlias
} AS Organization_Catalog_category1__info}
WITH collect(organization{.*, _id: id(organization), category1: Organization_Catalog_category1__info})[0..6] AS collectionAlias, count(organization) AS countAlias
RETURN {
  dataList: collectionAlias, totalCount: countAlias
} AS Order_Organization_buyer__info}
WITH collect(order{.*, _id: id(order), buyer: Order_Organization_buyer__info})[0..6] AS collectionAlias, count(order) AS countAlias
RETURN {
  dataList: collectionAlias, totalCount: countAlias
} AS OrderLine_Order_order__info}
RETURN orderline{.*, _id: id(orderline), order: OrderLine_Order_order__info}

[joshcornejo]

but you already have the nodes in variables (orderline / order / organization / catalog) ...?

[vrangana]

Our initial Query had the following relationship on the query, will this kind of relationship be supported with cypher statement which you suggested?

So from Organization node, we have multiple relationships to Catalog. So we need to extract data from each related Catalog node.

OrderLine -[:RELATES_TO {fieldName: 'order'}]-> Order -[:RELATES_TO {fieldName: 'buyer'}]-> Organization -[:RELATES_TO {fieldName: 'category1'}]-> catalog1:Catalog
         Organization-[:RELATES_TO {fieldName: 'category2'}]-> catalog2:Catalog
         Organization-[:RELATES_TO {fieldName: 'category3'}]-> catalog3:Catalog

[glilienfield (Gary Lilienfield)]

I agree with @joshcornejoc, there doesn’t seem to be a need to the CALL subqueries. Usually you use them for some type of processing a correlated query. Here you are just matching and passing the result.

I would look at your relationships. You have generic relationships with a parameter to differentiate the type. I would consider using different relationship types that identify the type. This should speed up the query as the execution will not need to get all the relationships with the general type attached to a node and iteratively filter them by a property (which also requires a property access), but instead filter by type directly when performing path expansion.

[vrangana]

Thanks @glilienfield and @joshcornejo for the valuable inputs.

1. @glilienfield : Regarding the suggestion to use different relationship types, I will discuss with the team if we can try this solution on our implementation.
2. @joshcornejo : When I used the option of using a single MATCH query to extract the data, the query was little bit faster (Earlier it was 9to10 secs, with MATCH query its coming around 5to6 secs). Me being a novice on Cypher queries I have the following queries.

Queries:
a) Does cypher query execution have performance impact when the query has multi level of relationships between the nodes (ex: OrderLine -> Order -> Organization -> Catalog = 4 levels) with the same relationship type (RELATES_TO)?

b) We currently have 136168 (OrderLines) -> 136154 (Order) -> 2802 (Organization) -> 40 (Catalog), even if we ran the nested query with relationship till Organization the query returned very quickly (below 5 secs). But as soon as we add the 4th level of relationship with Catalog for 3 different catalog relationship, the query execution timed out (as our time out is set as 30s). Is there a reason for this behavior?

MATCH (orderline:OrderLine {tenantId: '853357c4-28f3-4f69-b95a-7ee38363e798', type: 'OrderLine'})
      -[:RELATES_TO {fieldName: 'order'}]->(order:Order {tenantId:'853357c4-28f3-4f69-b95a-7ee38363e798'})
      -[:RELATES_TO {fieldName: 'buyer'}]->(organization:Organization {tenantId: '853357c4-28f3-4f69-b95a-7ee38363e798'})
      -[:RELATES_TO {fieldName: 'category1'}]->(catalog:Catalog {tenantId: '853357c4-28f3-4f69-b95a-7ee38363e798'})
      WHERE catalog.code = 'PG&S' AND (orderline.plannedShipDate >= '2023-01-01T06:00:00.000Z' AND orderline.plannedShipDate < '2023-01-31T06:00:00.000Z') AND (orderline.deleted = false OR orderline.deleted IS null )
    WITH collect(catalog{.*, _id: id(catalog)})[0..6] AS collectionCatalogAlias, count(catalog) AS countCatalogAlias,
    collect(organization{.*, _id: id(organization)})[0..6] AS collectionOrgAlias, count(organization) AS countOrgAlias,
    collect(order{.*, _id: id(order)})[0..6] AS collectionOrdAlias, count(order) AS countOrdAlias,
    collect(orderline{.*, _id: id(orderline)})[0..6] AS collectionOrdLinAlias, count(orderline) AS countOrdLinAlias
RETURN {
    dataListCatalog: collectionCatalogAlias, totalCountOrg: countCatalogAlias, 
    dataListOrg: collectionOrgAlias, totalCountOrg: countOrgAlias, 
    dataListOrd: collectionOrdAlias, totalCountOrd: countOrdAlias,
    dataListOrdLin: collectionOrdLinAlias, totalCountOrdLin: countOrdLinAlias
} AS QueryOutput__info

[joshcornejo]

You can think about it as a bag of items - the fewer labels/identifiers/differentiators, the longer the queries will take to "find" the correct elements in a query.

Your query needs to traverse more "paths" and waste execution time.

As @glilienfield said in his answer, you have only generic relationships, specifying relationships will "narrow" the search space

MATCH (orderline:OrderLine {tenantId: '853357c4-28f3-4f69-b95a-7ee38363e798', type: 'OrderLine'})
      -[:partOf {fieldName: 'order'}]->(order:Order {tenantId:'853357c4-28f3-4f69-b95a-7ee38363e798'})
      -[:belongsTo {fieldName: 'buyer'}]->(organization:Organization {tenantId: '853357c4-28f3-4f69-b95a-7ee38363e798'})
      -[:has {fieldName: 'category1'}]->(catalog:Catalog {tenantId: '853357c4-28f3-4f69-b95a-7ee38363e798'}, code:'PG&S')
      WHERE (orderline.plannedShipDate >= '2023-01-01T06:00:00.000Z' AND orderline.plannedShipDate < '2023-01-31T06:00:00.000Z') 
        AND (orderline.deleted = false OR orderline.deleted IS null )
    WITH collect(catalog{.*, _id: id(catalog)})[0..6] AS collectionCatalogAlias, count(catalog) AS countCatalogAlias,
         collect(organization{.*, _id: id(organization)})[0..6] AS collectionOrgAlias, count(organization) AS countOrgAlias,
         collect(order{.*, _id: id(order)})[0..6] AS collectionOrdAlias, count(order) AS countOrdAlias,
         collect(orderline{.*, _id: id(orderline)})[0..6] AS collectionOrdLinAlias, count(orderline) AS countOrdLinAlias
RETURN {
    dataListCatalog: collectionCatalogAlias, totalCountOrg: countCatalogAlias, 
    dataListOrg: collectionOrgAlias, totalCountOrg: countOrgAlias, 
    dataListOrd: collectionOrdAlias, totalCountOrd: countOrdAlias,
    dataListOrdLin: collectionOrdLinAlias, totalCountOrdLin: countOrdLinAlias
} AS QueryOutput__info

I am not sure what the reasoning is for all those collect and return aliases ... so perhaps the first question should be: what result set are you trying to build?

[vrangana]

@joshcornejo Thanks for your input. For now can we assume that we are using "RELATES_TO" as the only relationship type, can you please clarify on the below two queries.

cc: @glilienfield

Queries:
a) Does cypher query execution have performance impact when the query has multi level of relationships between the nodes (ex: OrderLine -> Order -> Organization -> Catalog = 4 levels) with the same relationship type (RELATES_TO)?

b) We currently have 136168 (OrderLines) -> 136154 (Order) -> 2802 (Organization) -> 40 (Catalog), If we ran the nested query with relationship till Organization the query returned very quickly (below 5 secs). But as soon as we add the 4th level of relationship with Catalog for 3 different catalog relationship, the query execution timed out (as our time out is set as 30s). Is there a reason for this behavior?

[joshcornejo]

Hello,

As we said - the less labels means the longer it will take to slice the sets. if you have a 1:N relationship as above, when you introduce L4 (Catalog) you are multiplying by 40 the number of relationships traversed as they are all :RELATES_TO.

[glilienfield (Gary Lilienfield)]

You should review the explain plan between the queries with and without the category to see if it explains the difference.

Also, do you need to specify the tenet ID for the intermediate nodes? I would imagine it is only needed for the anchor node of the query, as all the related nodes should be related to the same tenant.

[vrangana]

@glilienfield Good Day.

I am currently comparing the EXPLAIN plans for different types of nested queries (with and without Catalog code values). I have uploaded the explain plans for your reference.

ExplainPlan_WIth_WithoutCategoryCode.txt | Powered by Box

For your query: Yes, tenantId specified on the anchor node and its related nodes will be for the same tenant id only.

[vrangana]

Yes I do see a nested child entries when additional category1/2/3.codes are used.

Following are the child entries for only category1.code (when only this field is added to the nested query)

{
    "children": [
        {
            "children": [
                {
                    "children": [
                        {
                            "children": [
                                {
                                    "children": [
                                        {
                                            "children": [
                                                {
                                                    "children": [],
                                                    "Details": "organization",
                                                    "EstimatedRows": 0,
                                                    "identifiers": [
                                                        "organization"
                                                    ],
                                                    "operatorType": "Argument@neo4j"
                                                }
                                            ],
                                            "Details": "organization:Organization",
                                            "EstimatedRows": 0,
                                            "identifiers": [
                                                "organization"
                                            ],
                                            "operatorType": "Filter@neo4j"
                                        }
                                    ],
                                    "Details": "(organization)-[anon_5:RELATES_TO]->(catalog)",
                                    "EstimatedRows": 0,
                                    "identifiers": [
                                        "catalog",
                                        "organization",
                                        "anon_5"
                                    ],
                                    "operatorType": "Expand(All)@neo4j"
                                }
                            ],
                            "Details": "anon_5.fieldName = \"category1\" AND catalog:Catalog AND catalog.tenantId = \"853357c4-28f3-4f69-b95a-7ee38363e798\"",
                            "EstimatedRows": 0,
                            "identifiers": [
                                "catalog",
                                "organization",
                                "anon_5"
                            ],
                            "operatorType": "Filter@neo4j"
                        }
                    ],
                    "Details": "catalog",
                    "EstimatedRows": 0,
                    "identifiers": [
                        "catalog"
                    ],
                    "operatorType": "Distinct@neo4j"
                }
            ],
            "Details": "count(catalog) AS anon_51, collect(catalog{_id: id(catalog), .*}) AS anon_52",
            "EstimatedRows": 0,
            "identifiers": [
                "anon_52",
                "anon_51"
            ],
            "operatorType": "EagerAggregation@neo4j"
        }
    ],
    "Details": "anon_52[0..6] AS collectionAlias, anon_51 AS countAlias",
    "EstimatedRows": 0,
    "identifiers": [
        "countAlias",
        "collectionAlias"
    ],
    "operatorType": "Distinct@neo4j"
}