EXPLAIN vs PROFILE, Logical/Physical Operators, Rows, Database Hits, Memory, and Pipeline Interpretation

Learning outcomes

AtlasMart has a query that “sometimes feels slow,” but elapsed time alone cannot tell whether the problem is an expensive starting scan, fan-out after a good seek, a blocking sort, an aggregation buffer, a cache-state difference, or simply a larger result. This lesson turns the plan into the primary diagnostic artifact.

1. EXPLAIN predicts; PROFILE executes

EXPLAIN compiles a query and returns its plan without running it, so the plan contains estimates. PROFILE executes the query and augments the plan with runtime measurements such as actual rows and database hits. This distinction is operationally important: if the query contains writes, PROFILE performs those writes. Start with EXPLAIN, especially when investigating an unfamiliar statement.

2. Read the tree from the leaves upward

The bottom operators find starting entities; their parents filter, expand, join, aggregate, sort and finally produce results. A plan is a binary tree: a leaf has no children, unary operators transform one stream, and join/apply operators may have two inputs. The row flow is upward even though the table is printed top-down.

CYPHER 25CREATE CONSTRAINT customer_id IF NOT EXISTS FOR (c:Customer) REQUIRE c.customerId IS UNIQUE;CREATE CONSTRAINT order_id IF NOT EXISTS FOR (o:Order) REQUIRE o.orderId IS UNIQUE;CREATE CONSTRAINT product_id IF NOT EXISTS FOR (p:Product) REQUIRE p.productId IS UNIQUE;UNWIND range(1,100) AS iMERGE (c:Customer {customerId:'CH10-C-'+right('0000'+toString(i),4)})SET c.name='Plan Customer '+toString(i),    c.segment=CASE WHEN i=1 THEN 'HOT' WHEN i % 10=0 THEN 'VIP' ELSE 'STANDARD' END,    c.labTag='ch10';UNWIND range(1,200) AS iMERGE (p:Product {productId:'CH10-P-'+right('0000'+toString(i),4)})SET p.name='Plan Product '+toString(i),    p.categoryCode=CASE WHEN i % 10 < 6 THEN 'CAM' WHEN i % 10 < 8 THEN 'PWR' WHEN i % 10 = 8 THEN 'ACC' ELSE 'OUT' END,    p.price=toFloat(20 + (i % 180)),    p.labTag='ch10';UNWIND range(1,2000) AS iWITH i, CASE WHEN i <= 800 THEN 1 ELSE 2 + ((i-801) % 99) END AS customerNoMATCH (c:Customer {customerId:'CH10-C-'+right('0000'+toString(customerNo),4)})MERGE (o:Order {orderId:'CH10-O-'+right('0000'+toString(i),4)})SET o.status=CASE WHEN i % 7=0 THEN 'REFUNDED' WHEN i % 3=0 THEN 'SHIPPED' ELSE 'PAID' END,    o.createdAt=datetime({epochSeconds:1735689600 + i*60}),    o.labTag='ch10'MERGE (c)-[:PLACED]->(o)WITH i,oMATCH (p1:Product {productId:'CH10-P-'+right('0000'+toString(1 + ((i-1) % 200)),4)})MATCH (p2:Product {productId:'CH10-P-'+right('0000'+toString(1 + ((i*7-1) % 200)),4)})MERGE (o)-[r1:CONTAINS {lineId:'CH10-L-'+right('0000'+toString(i),4)+'-A'}]->(p1)SET r1.quantity=1 + (i % 3),r1.labTag='ch10'MERGE (o)-[r2:CONTAINS {lineId:'CH10-L-'+right('0000'+toString(i),4)+'-B'}]->(p2)SET r2.quantity=1,r2.labTag='ch10';CREATE RANGE INDEX ch10_product_category_price IF NOT EXISTSFOR (p:Product) ON (p.categoryCode,p.price);CREATE RANGE INDEX ch10_order_status IF NOT EXISTSFOR (o:Order) ON (o.status);CALL db.awaitIndexes(300);

CYPHER 25MATCH (c:Customer {labTag:'ch10'}) WITH count(c) AS customersMATCH (p:Product {labTag:'ch10'}) WITH customers,count(p) AS productsMATCH (o:Order {labTag:'ch10'}) WITH customers,products,count(o) AS ordersMATCH ()-[r:CONTAINS {labTag:'ch10'}]->()RETURN customers,products,orders,count(r) AS containsRelationships;MATCH (c:Customer {labTag:'ch10'})-[:PLACED]->(o:Order {labTag:'ch10'})RETURN c.customerId,count(o) AS ordersORDER BY orders DESC,c.customerIdLIMIT 5;

3. Baseline one query before tuning it

Use the same query text and parameters for the paired plan capture. The fixture intentionally gives CH10-C-0001 800 orders while the remaining 99 customers share the other 1200 orders. This makes graph-shape skew visible without needing a production-sized store.

:param customerId => "CH10-C-0001";:param category => "CAM";:param minPrice => 80.0;

CYPHER 25EXPLAINMATCH (c:Customer {customerId:$customerId})-[:PLACED]->(o:Order)-[r:CONTAINS]->(p:Product)WHERE p.categoryCode=$category AND p.price >= $minPriceRETURN count(DISTINCT o) AS orders,       count(*) AS lineRows,       sum(r.quantity) AS units;

CYPHER 25PROFILEMATCH (c:Customer {customerId:$customerId})-[:PLACED]->(o:Order)-[r:CONTAINS]->(p:Product)WHERE p.categoryCode=$category AND p.price >= $minPriceRETURN count(DISTINCT o) AS orders,       count(*) AS lineRows,       sum(r.quantity) AS units;

4. What the numbers prove—and what they do not

5. Deliberately wrong: tune from elapsed time alone

Run the same query twice and you may observe a faster second execution because planning, page cache, filesystem cache, JVM state and other conditions changed. The repair is to record the plan, input parameters, graph size/degree distribution, cache/warmup protocol, result cardinality and a latency distribution. Change one dimension at a time.

Production judgment and next step

Do not optimize for the prettiest operator name. Optimize an observable workload: correctness first, then cardinality, work, memory, tail latency and maintainability. Next, we explain why the same syntax can produce radically different work when graph degree and selectivity differ.

Summary and next step

EXPLAIN vs PROFILE, Logical/Physical Operators, Rows, Database Hits, Memory, and Pipeline Interpretation is useful only when its assumptions and observed evidence stay attached to the decision. The examples above establish a reproducible mechanism and boundary; they do not turn one lab result into a universal production rule.

Next, continue to Cardinality Estimation, Selectivity, Starting Points, Expand Operators, and Why Graph Shape Matters. Carry forward the verified assumptions, fixture state, version/edition boundaries, and measurements from this lesson instead of treating the next topic as an isolated recipe.

Authoritative references

• Current Neo4j versions — Release/LTS snapshot used for this chapter.
• Understanding query plans — EXPLAIN/PROFILE, plan columns, rows, DB hits, memory and plan reading.
• Operators — Current operator families and operator semantics.
• Operators in detail — Current seek/scan, expand, apply/join, eager, sort and aggregation operators.
• Statistics and execution plans — Statistics collection, selectivity, replanning thresholds and manual preparation.
• Query caches — Per-database query caches, cache sizing and current query-size behavior.
• Cypher runtimes — Slotted, pipelined and parallel runtime boundaries.
• Basic query tuning example — A current end-to-end plan-reading and index-tuning example.
• Runtime concepts — Community slotted versus Enterprise/Aura pipelined and parallel runtime behavior.