Batch processing is used when ingesting large amounts of data. Some native examples of batch processing are when using CALL {} IN TRANSACTIONS (in Neo4j 4.4.x or later) or when using USING PERIODIC COMMIT LOAD CSV (in Neo4j 4.4.x or earlier) or CALL apoc.periodic.iterate() when using APOC Procedures.

Since the batch queries often execute many times, it is important that these are as efficient as possible. Most batch queries perform MATCH or MERGE operations to existing parts of the graph, and so it is very important that these lookups are backed up by indexes.

 

Evaluating lookup operators in an EXPLAIN plan

To make use of indexes, once they are created, you must make sure that the label and property (or properties) for the index are present in the MATCH or MERGE patterns used by your batch query. You can double-check for index usage by viewing the query's EXPLAIN plan:

https://support.neo4j.com/s/article/6638160188691-How-to-get-Cypher-query-execution-plans-using-EXPLAIN-and-PROFILE

You should make sure that only index-related operators (such as NodeIndexSeek) are being used for the lookups associated with your MATCH and MERGE elements of your batch query. 

You do not want to see AllNodesScan or NodeByLabelScan operators for MATCH or MERGE elements of your batch, otherwise the batching may become so slow that it is unable to complete in any reasonable amount of time. 

The presence of an AllNodesScan means that the lookup is accomplished by evaluating every single node in the database, and has the worst performance of all. Seeing an AllNodesScan in the plan typically means that either you did not supply a label in the MATCH or MERGE pattern (the presence of only a label should allow for a marginally better NodeByLabelScan) or otherwise that you are missing a Node Lookup Index, which is a special index (which should nearly always exist) that allows for label scans.

https://neo4j.com/docs/cypher-manual/current/indexes/search-performance-indexes/managing-indexes/#create-a-lookup-index-only-if-it-does-not-already-exist

The presence of a NodeByLabelScan means that the lookup is accomplished by evaluating every single node of the given label, and this will be intolerably slow when batching. Seeing a NodeByLabelScan in the plan typically means that either such an index doesn't exist, or not every property of the index is present in the pattern. Please remember that when you have a composite index (an index comprised of multiple properties), all properties must be present in some way in order for the index to be used. 

The presence of NodeByIdSeek is fine, and this is actually the fastest means of lookup, looking the node up directly by its id(). This typically means that you performed some different means of looking up the node, and kept its id() for a later lookup. We do not recommend saving id()s outside of the db for later lookup, as there are some operations that can cause ids to change...and if you deleted the element with that id, it is possible that id could have been reused for some new data, and so may be stale and point to something completely different than what was referenced before. 

 

Verify that all your indexes are online

If you do not see an expected NodeByIdSeek (or similar index lookup operator) when you know that such an index already exists and should have been planned, it is possible that the index may be offline.

You can use the following to check for any indexes that are not online:

SHOW INDEXES WHERE state <> "ONLINE"

In some rare cases, the index may either be completely offline, or may be stuck in a "POPULATING" state but unable to progress. In these situations, it may be best to DROP the index and recreate it. You can repeat the above show indexes command to check on the population progress. Once it is back online, please run another EXPLAIN plan and see if the plan shows usage of the index.

 

EXPLAIN for native queries

For native queries, as when using CALL {} IN TRANSACTIONS or USING PERIODIC COMMIT LOAD CSV, you can run an EXPLAIN on the entire query...however you will need to locate the part of the plan associated with the batching to verify that no NodeByLabelScan or AllNodesScan operators are present. 

The batching part of a query is usually on the right-side brach of an Apply or TransactionForeach operator.

 

EXPLAIN for apoc.periodic.iterate()

Because apoc.periodic.iterate() is a procedure, an EXPLAIN of the query making this procedure call will give little to no useful information, as the String queries supplied as arguments to the procedure cannot be evaluated by EXPLAIN. Instead, you will need to copy and paste out the string queries and run separate EXPLAINs on those.

The driving query

The driving query is the first query string argument to apoc.periodic.iterate(), and pulls the data that will be handed off to the batching query for processing. You should be able to EXPLAIN the driving query without trouble. As this query is not the batching query, it is okay if you see NodeByLabelScan in the plan if the idea is to process all nodes of a given label (though please make sure that this is expected...if you were attempting a lookup via a property, then you may want to check if you need to create a supporting index for faster lookup). If the processing must include all nodes of your graph, then you may see an AllNodesScan. 

The main concern for the driving query are to ensure that there are no eager operators or eager aggregations in the mainline of the plan (between the left-most operator and the bottom operator), as that can be an obstacle to proper batching, evidence of a logical requirement in the query that requires eagerly evaluating all rows of the query up to that point.

It is also important to check for best practices for apoc.periodic.iterate(), as not following best practices could disable batching behavior, and can even cause you to go out of heap memory:

https://neo4j.com/developer/kb/a-significant-change-in-apoc-periodic-iterate-in-apoc-4-0/

The batch query

The batching query is the second query string argument to apoc.periodic.iterate(), and it is called per batch of data provided by the driving query. The batching query should be as efficient as possible, otherwise the performance for the entire operation may become untenable. You must ensure the batch query has no NodeByLabelScan or AllNodesScan operators for efficiency.

However, simply copying and pasting out the batch query to perform an EXPLAIN will produce errors, and this is because there are some things that the procedure is doing under-the-hood for your convenience with respect to the parameters and variables being used, and we must manually make those changes to the query so we can EXPLAIN it successfully.

In particular, the procedure is taking the returned variables from the driving query, passing them as parameters to the batch query, and aliasing those parameters as variables so you can use them as variables throughout the batch query. For our EXPLAIN to work, we must add some Cypher to alias those parameters as variables.

As an example, take this batch delete query:

CALL apoc.periodic.iterate("
  MATCH (n:TO_DELETE)
  RETURN n.uuid as uuid
","
  MATCH (n:TO_DELETE)
  WHERE n.uuid = uuid
  DETACH DELETE n
", {}) YIELD batches, total, errorMessages

First, note that we are following best practices by only returning properties or id()s from the driving query, instead of the node or relationship variables themselves (which would cause memory problems by disabling the batching if we did so).

If we did an EXPLAIN of the batch query, it would error, because uuid is not in scope. We need to add the aliasing of the $uuid parameter to the uuid variable. Here's our EXPLAIN with this change:

EXPLAIN  
WITH $uuid as uuid
MATCH (n:TO_DELETE)
WHERE n.uuid = uuid
DETACH DELETE n

This should be able to successfully produce an EXPLAIN plan. We would want to confirm that a NodeIndexSeek (or similar) is being used to lookup n, and not a NodeByLabelScan or an AllNodesScan.

Related Articles

Batch large transactions