Diagnoses and fixes slow Neo4j Cypher queries by reading execution plans, identifying bad operators (AllNodesScan, CartesianProduct, Eager, NodeByLabelScan), and prescribing fixes (indexes, hints, query rewrites, runtime selection). Use when a query is slow, when EXPLAIN or PROFILE output needs inte

When to Use Query takes unexpectedly long; need root cause analysis EXPLAIN/PROFILE output in hand — needs interpretation Identifying which index is missing or unused Deciding between slotted / pipelined / parallel runtimes Monitoring live queries: SHOW QUERIES, SHOW TRANSACTIONS Cardinality estimates wrong (plan replanning needed) When NOT to Use Writing Cypher from scratch → neo4j cypher skill GDS algorithm performance → neo4j gds skill Schema design / data modelling → neo4j modeling skill EXPLAIN vs PROFILE EXPLAIN PROFILE Executes query? No Yes Returns data? No Yes Shows rows (actual) No Yes Shows dbHits (actual) No Yes Shows estimatedRows Yes Yes Cost Zero Full query cost Run PROFILE twice — first run warms page cache; second gives representative metrics. Query API alternative (no driver): Key Plan Metrics Metric Good Investigate if dbHits Low; drops after index added High relative to rows rows Shrinks early in plan Large until final operator estimatedRows Close to rows 10× divergence from actual pageCacheHitRatio 0.99 <0.90 (disk I/O bottleneck) pageCacheHits High — pageCacheMisses Near 0 Rising (page cache too small) Read plans bottom up — leaf operators at bottom initiate data retrieval. Operator Reference Operator Good/Bad Meaning Fix NodeIndexSeek ✓ Exact match via RANGE/LOOKUP index — NodeUniqueIndexSeek ✓ Unique constraint index hit — NodeIndexContainsScan ✓ TEXT index CONTAINS / STARTS WITH — NodeIndexScan ~ Full index scan (no predicate) Add WHERE predicate or composite index NodeByLabelScan ✗ Scans all nodes of label Add RANGE index on lookup property AllNodesScan ✗✗ Scans entire node store Add label + index to MATCH Expand(All) ~ Traverse relationships from node Normal; limit with LIMIT or WHERE Expand(Into) ~ Find rels between two matched nodes Normal for known endpoint joins Filter ~ Predicate applied after scan Move predicate into WHERE with index CartesianProduct ✗ No join predicate between two MATCH Add WHERE join or use WITH between MATCHes NodeHashJoin ~ Hash join on node IDs Normal; planner chose hash join ValueHashJoin ~ Hash join on values Normal; watch memory for large inputs EagerAggregation ~ Full aggregation (ORDER BY, count( )) Normal for aggregates Aggregation ✓ Streaming aggregation — Eager ✗ Read/write conflict; materialises all rows See Eager fix strategies below Sort ~ Full sort — O(n log n) Add LIMIT before Sort; push LIMIT earlier Top ✓ Sort+Limit combined — O(n log k) Preferred over Sort+Limit Limit ✓ Truncates rows early Push as early as possible Skip ~ Offset pagination Use keyset pagination on large graphs ProduceResults — Final output operator Root of tree UndirectedRelationshipByIdSeekPipe ~ Lookup by relationship ID Avoid id(r) — use elementId(r) Full operator reference → [references/plan operators.md](references/plan operators.md) Diagnostic Workflow (Agent Runbook) Step 1 — Baseline Plan Scan output for AllNodesScan , NodeByLabelScan , CartesianProduct , Eager . Step 2 — Check Indexes Find whether the label/property from the bad operator has an index. Step 3 — Create Missing Index Wait for state = 'ONLINE' before measuring. Step 4 — Profile After Fix Compare dbHits and elapsed ms before/after. Target: NodeIndexSeek replaces scan operators. Step 5 — Stale Statistics (if estimatedRows wildly off) Config: dbms.cypher.statistics divergence threshold (default 0.75 — plan expires when stat changes 75%). Fixing Common Plan Problems Missing Index → NodeByLabelScan / AllNodesScan Wrong Anchor — Planner Picks Wrong Starting Node Reorder MATCH or use hints: CartesianProduct — Two Unconnected MATCHes Eager — Read/Write Conflict Three strategies (pick simplest): 1. Add specific labels to MATCH nodes so planner distinguishes read/write sets 2. Collect then write : WITH collect(n) AS nodes UNWIND nodes AS n SET n.x = 1 3. CALL IN TRANSACTIONS : isolates each batch in its own transaction Expensive CONTAINS / ENDS WITH Over Traversal — Push LIMIT Early Cypher Runtime Selection Runtime Select Best For Avoid When pipelined CYPHER runtime=pipelined Default OLTP; streaming, low memory Unsupported operators fall back to slotted slotted CYPHER runtime=slotted Guaranteed stable behavior; debug Performance critical OLTP parallel CYPHER 25 runtime=parallel Large analytical scans; aggregations OLTP, writes, short queries, Aura Free Pipelined is default for most queries. Parallel requires dbms.cypher.parallel.worker limit configured; available on Enterprise and Aura Pro 2025+. Query Monitoring Commands Full monitoring reference → [references/stats and monitoring.md](references/stats and monitoring.md) Checklist [ ] Run EXPLAIN first — identifies plan problems without execution cost [ ] Check for AllNodesScan / NodeByLabelScan — missing index [ ] Check for CartesianProduct — missing join predicate [ ] Check for Eager — read/write conflict [ ] SHOW INDEXES — confirm relevant index exists and state = 'ONLINE' [ ] Create missing index; wait for ONLINE [ ] Run PROFILE twice — first warms cache, second is representative [ ] Compare dbHits before/after fix [ ] If estimatedRows wildly off → CALL db.prepareForReplanning() [ ] Push LIMIT / WITH n LIMIT k before high fanout operations [ ] For CONTAINS/ENDS WITH — TEXT index, not RANGE [ ] For large analytical queries — consider runtime=parallel [ ] Kill long running queries with TERMINATE TRANSACTION

neo4j-query-tuning-skill