Query plans, produced by EXPLAIN and PROFILE appended to the start of a query, can reveal important information about how the plan will be executed (or how it was executed, in PROFILE's case), but only if you can read the plan and interpret what it is telling you.

EXPLAIN - Produces a query plan but does not execute the query. Estimated rows are shown, but these should only be taken as rough approximations. Estimated rows can help the query planner to choose its plan, but they aren't very useful when performing your own query tuning.

PROFILE - Executes the query and produces a query plan for it. Db hits are shown, as are the rows resulting from each operation, the memory used, and the pagehits and page misses. While db hits can be used to draw attention to certain operators in the plan, the number of rows is often the more useful metric here. Since most Cypher operators execute per row, the number of rows strongly correlates with the amount of work that needs to be done by the next operator. Spikes in rows in a plan can often point out the most expensive parts of the query execution.

 

Reading cypher-shell query plans

Our official documentation on Reading Execution Plans describes more about how to read a query plan when using cypher-shell.
 

+-------------------+----+------------------------------------------------------------------------+----------------+---------------------+
| Operator          | Id | Details                                                                | Estimated Rows | Pipeline            |
+-------------------+----+------------------------------------------------------------------------+----------------+---------------------+
| +ProduceResults   |  0 | `count(*)`                                                             |              1 | In Pipeline 3       |
| |                 +----+------------------------------------------------------------------------+----------------+---------------------+
| +EagerAggregation |  1 | count(*) AS `count(*)`                                                 |              1 |                     |
| |                 +----+------------------------------------------------------------------------+----------------+                     |
| +Filter           |  2 | NOT anon_1 = anon_5 AND anon_0.name = $autostring_0 AND anon_0:Station |              0 |                     |
| |                 +----+------------------------------------------------------------------------+----------------+                     |
| +Expand(All)      |  3 | (d)-[anon_1:CALLS_AT]->(anon_0)                                        |              0 |                     |
| |                 +----+------------------------------------------------------------------------+----------------+                     |
| +Filter           |  4 | d:Stop                                                                 |              0 |                     |
| |                 +----+------------------------------------------------------------------------+----------------+                     |
| +NullifyMetadata  | 14 |                                                                        |              0 |                     |
| |                 +----+------------------------------------------------------------------------+----------------+                     |
| +Repeat(Trail)    |  5 | (a) (...){1, *} (d)                                                    |              0 | Fused in Pipeline 2 |
| |\                +----+------------------------------------------------------------------------+----------------+---------------------+
| | +Filter         |  6 | isRepeatTrailUnique(anon_8) AND anon_7:Stop                            |              6 |                     |
| | |               +----+------------------------------------------------------------------------+----------------+                     |
| | +Expand(All)    |  7 | (anon_9)<-[anon_8:NEXT]-(anon_7)                                       |              6 |                     |
| | |               +----+------------------------------------------------------------------------+----------------+                     |
| | +Filter         |  8 | anon_9:Stop                                                            |             11 |                     |
| | |               +----+------------------------------------------------------------------------+----------------+                     |
| | +Argument       |  9 | anon_9                                                                 |             13 | Fused in Pipeline 1 |
| |                 +----+------------------------------------------------------------------------+----------------+---------------------+
| +Filter           | 10 | a:Stop                                                                 |              0 |                     |
| |                 +----+------------------------------------------------------------------------+----------------+                     |
| +Expand(All)      | 11 | (anon_6)<-[anon_5:CALLS_AT]-(a)                                        |              0 |                     |
| |                 +----+------------------------------------------------------------------------+----------------+                     |
| +Filter           | 12 | anon_6.name = $autostring_1                                            |              1 |                     |
| |                 +----+------------------------------------------------------------------------+----------------+                     |
| +NodeByLabelScan  | 13 | anon_6:Station                                                         |             10 | Fused in Pipeline 0 |
+-------------------+----+------------------------------------------------------------------------+----------------+---------------------+

Such plans must be read, bottom-up, using the bottom-leftmost track as the starting point, reading upwards along that track until a branching point is reached. At that branch, we look down the right-hand side of that branch, find that track's bottom-leftmost operator, and start there, reading upwards, until another branch point is reached and the process is repeated.

Using the aforementioned approach, the above query plan, would be read as follows, showing the operator and it's ID:

NodeByLabelScan (13)
Filter (12)
Expand(All) (11)
Filter (10)
Repeat(Trail) (5) (this is a branch point, indicating the need to jump to the start of the right-side branch)
Argument (9) (these are the arguments to the right-side branch)
Filter (8)
Expand(All) (7)
Filter (6)
Repeat(Trail) (5) (indicates completion of both sides of this operator)
NullifyMetadata (14)
Filter (4)
Expand(All) (3)
Filter (2)
EagerAggregation (1)
ProduceResults (0)

Note that this only shows the ordering of operators with respect to each other. It does not necessarily show the order of execution with respect to input rows compared to each other. One may usually assume, in "lazy" execution, that each row is to be processed with trace through the plan prior to the next, excepting when we reach an Eager or EagerAggregation or any Operators which show an "Eager" in the far-right table entry for their row. These operators aggregate all rows prior to anything beyond them executing. This is discussed in further detail, in the official Cypher documentation on lazy and eager query evaluation .

Reading Neo4j Browser query plans

Visual query plans produced by the Neo4j Browser look a little different than the tables produced by cypher-shell. These visual plans appear much more splayed, and it is often trickier to trace through them, especially for plans of very complex Cypher queries. 

While they are read in a similar manner to the cypher-shell query plans, they are read from the top-down instead of the bottom-up. That is, the ProduceResults or Result operator at the end appears at the bottom of the visual plan, instead of at the top when viewing the cypher-shell query plan.

To find the starting operator of the plan, one must trace, from the ending operator, the leftmost path until a leaf node is reached. Due to the splayed nature of the visual query plans, this first operator may not be the operator that appears to be the top-leftmost in the plan. That is because forks that occur branch off to the right, and if large enough, those forked paths may grow higher than the main leftmost branch of the plan, and as such, may take up much of the upper area of the plan, likely giving the appearance of the start of the plan when instead, they are actually part of forked off branches of the plan.

From the starting operator, the plan would be read downwards, operator-by-operator until a branching operator is reached. We have our leftmost branch (that's where we've been reading down from), so now we need to read through the branch on the right side. 

To find that, we would trace upward along the right branch, looking for the top-leftmost operator which is a leaf node (this is similar to what we did to find the first operator in the plan). These leaf nodes are usually Argument operators (from already-available elements of the input rows from the left-side of the operator), or they may be operators used for node or relationship lookup, such as NodeByLabelScan, NodeIndexSeek, AllNodesScan, or similar. From this starting node of the right branch, we would read downward operator-by-operator until we reach another branching operator (in which case we again shift to the branch on its right side and repeat, finding where that branch starts and reading through), or we finally reach the end of that  right branch, and now with both branches read we can continue down beyond it.

An example EXPLAIN plan, annotated with the ordering of the operators (visiting branching operators only once), is show below.


It is important to note that the first operator, the LoadCSV, is the top-leftmost branch (which is the most important thing), but it is not the top-leftmost operator positionally compared to the others. As you can see, operators 3 and 4, though appearing above the first operator, are actually occurring along a right branch of the plan, branched right from operator 2, and then left from operator 5.
 

What should I watch out for in a query plan?

Query tuning when referencing a plan can be a complex thing, but here are a few of the more common things to watch for:

Unexpected expensive lookups

If you are expecting index lookups, but instead you are seeing NodeByLabelScan or, even worse, AllNodesScan operators, then this may be an indication that you do not have indexes or unique constraints in place to support the MATCH or MERGE operations in your query, or something in your query may be barring their use. 

For example, an index cannot be used if the node in the pattern does not have the label for the index present. Or, if you have a composite index created, it can't be used if only a subset of the properties for that index are present for consideration in the predicates for that node or relationship. Or it just might be that the boolean logic used in a WHERE clause may not logically allow usage of an index, as it would not be logically guaranteed to hold all potential results.

These more expensive operators can be particularly problematic in load queries. Because operators execute per row, a NodeByLabelScan, for example, may mean that for every row needing to be processed (say from a CSV), that each row would do its own separate NodeByLabelScan and filter to find the node for lookup. This can be far more expensive than a NodeIndexSeek, and can be the difference between a fast load and one that may run for multiple hours or may not be able to finish.
 

Eager operators along the main branch of the plan

Eager operators are present for correctness, and to preserve Cypher semantics. But if they occur in the main branch of your query plan (on the branch between the first operator and the last operator), they may be a cause of problems for the query, usually due to heap memory usage (since the partial results of all rows must be held in heap at those Eager operators).

It is far better, if possible, to have a query where Eager operators are tucked away into the right branches of query plans, preferably in the right branch of Apply or SubqueryForeach operators, as that greatly narrows down the scope for which the Eager behavior applies: per row processed in that right-side branch, instead of for all rows processing throughout the entire query execution. This is one reason why subquery CALL {} is so important to query tuning: they enforce per-row-foreach semantics, and scope down the the impact of aggregations and Eagers to individual executions of the subquery.

Spiking row counts

As mentioned above, the rows in a PROFILE plan are one of the strongest indicators of work being performed, since operations execution per row. Rows that spike extremely high can often indicate performance bottlenecks, for which query tuning may be needed.