Procedure calls cannot be easily profiled by EXPLAIN or PROFILE, since profiling can't see into a procedure call. apoc.periodic.iterate() from APOC Procedures is particularly problematic, as it is important to verify the query plans are optimal and don't include unexpected eager operations.

apoc.periodic.iterate() has this signature:

CALL apoc.periodic.iterate(<cypherIterate string>, <cypherAction string>, <options>) 
     YIELD <yielded variables>

The query containing the apoc.periodic.iterate() call can't be profiled in full, since the query strings are just strings, and not able to be profiled. However, we can take the query strings out and profile them individually.

Profiling the driving query

The cypherIterate query is the driving query, this generates rows of results which will ultimately be processed in batches of batchSize (from the option parameter map, default 10000). It is very important to ensure that there are no Eager (or eager behaving) operators in the cypherIterate plan, or at least to ensure such operators only apply to a very controlled and small number of rows, and not the entire set of all rows to process. This is because Eager operators materialize all row results in heap memory, within the relevant scope. Since the procedure is used to process results in batches, materializing all result rows in the driving query before any batching takes place can cause problematic memory pressure. This can defeat the entire reason for batching in the first place.

Profiling the cypherIterate query is easy enough: copy the query string contents, and profile that using EXPLAIN or PROFILE. Aside from removing the containing quotes, no changes should be needed.

Profiling the batching query

The cypherAction query is the batching query, executed once per batch of data pulled from the driving query's results. This query is harder to profile, as attempting the same approach (copying the query string contents and adding an EXPLAIN in front of it) will produce errors. This is because the apoc.periodic.iterate() procedure code adds to the cypherAction query string before executing it in order to make it work with the intended batching. Therefore, to profile the cypherAction query, we need to manually modify the query with the same changes before we can profile it.

Notably, apoc.periodic.iterate() prepends two things to its cypherAction query. 

1. It adds UNWIND $_batch as _batch , which is instrumental to handle the incoming batch of data to process. Since the batch is passed as a parameter list of map objects, it must be unwound back to rows for use within the query.
2. For each variable returned in the cypherIterate query, the procedure aliases that variable from the _batch after the UNWIND, exposing it as a variable that you can use in the cypherAction query.

Let's take this iterate() call as an example:

CALL apoc.periodic.iterate(
   "MATCH (m:Movie) RETURN id(m) as mId",
   "MATCH (m) WHERE id(m) = mId DETACH DELETE m", {}) YIELD batches, total, errorMessages

We would get an error if we tried to EXPLAIN the cypherAction query because mId is an unknown variable. For this to work, we need to modify the query in the same way that the iterate() procedure would do so to make it work.

In this case, since mId is the only returned variable from the cypherIterate query, the procedure prepends the query with:

UNWIND $_batch as _batch
WITH _batch.mId as mId

With these changes, we can use EXPLAIN to profile the same query that apoc.periodic.iterate() will execute. 

So in all, the full cypherAction query for profiling would be:

EXPLAIN
UNWIND $_batch as _batch
WITH _batch.mId as mId
MATCH (m) WHERE id(m) = mId
DETACH DELETE m

If there were other variables returned, then those also would be exposed and aliased as local variables in the same WITH clause.

As far as Eager goes in profiling the batch query, it's actually very common to see Eager operators here, and it might not be possible to remove Eagers from the batch query plan. If the number of rows remains controlled, it's fine for the Eager to remain. The batching should be what makes the Eager less impactful, provided that row cardinality is being controlled. 

If additional MATCHes and UNWINDs are used within the batch query, then perhaps subqueries can be used to further scope the impact of Eager operations. Still, it's best to have some general estimate of how many rows can be in scope for Eager operations. 

 

Additional References:

Execution plans and query tuning
apoc.periodic.iterate() documentation
Lazy and Eager query evaluation
How to read a query plan