Neo4j Query Tuning Skill

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

Run PROFILE twice — first run warms page cache; second gives representative metrics.

EXPLAIN MATCH (p:Person {email: $email}) RETURN p.name
PROFILE MATCH (p:Person {email: $email}) RETURN p.name

Query API alternative (no driver):

curl -X POST https://<host>/db/<db>/query/v2 \
  -u <user>:<pass> -H "Content-Type: application/json" \
  -d '{"statement": "EXPLAIN MATCH (p:Person {email: $email}) RETURN p.name", "parameters": {"email": "a@b.com"}}'

---

Key Plan Metrics

Read plans bottom-up — leaf operators at bottom initiate data retrieval.

---

Operator Reference

Full operator reference → references/plan-operators.md

---

Diagnostic Workflow (Agent Runbook)

Step 1 — Baseline Plan

EXPLAIN <query>

Scan output for AllNodesScan, NodeByLabelScan, CartesianProduct, Eager.

Step 2 — Check Indexes

SHOW INDEXES YIELD name, type, labelsOrTypes, properties, state
WHERE state = 'ONLINE'

Find whether the label/property from the bad operator has an index.

Step 3 — Create Missing Index

// RANGE index for equality/range predicates:
CREATE INDEX person_email IF NOT EXISTS FOR (n:Person) ON (n.email)
// TEXT index for CONTAINS/ENDS WITH:
CREATE TEXT INDEX person_bio IF NOT EXISTS FOR (n:Person) ON (n.bio)
// Composite for multi-property lookup:
CREATE INDEX order_status_date IF NOT EXISTS FOR (n:Order) ON (n.status, n.createdAt)

Wait for state = 'ONLINE' before measuring.

Step 4 — Profile After Fix

PROFILE <query>

Compare dbHits and elapsed ms before/after. Target: NodeIndexSeek replaces scan operators.

Step 5 — Stale Statistics (if estimatedRows wildly off)

CALL db.prepareForReplanning()
// or resample a specific index:
CALL db.resampleIndex("person_email")
// or resample all outdated:
CALL db.resampleOutdatedIndexes()

Config: dbms.cypher.statistics_divergence_threshold (default 0.75 — plan expires when stat changes >75%).

---

Fixing Common Plan Problems

Missing Index → NodeByLabelScan / AllNodesScan

// Force index hint when planner ignores it:
MATCH (p:Person {email: $email})
USING INDEX p:Person(email)
RETURN p.name
// Force label scan (sometimes faster for high selectivity):
MATCH (p:Person {email: $email})
USING SCAN p:Person
RETURN p.name

Wrong Anchor — Planner Picks Wrong Starting Node

Reorder MATCH or use hints:

// Force join at specific node:
MATCH (a:Author)-[:WROTE]->(b:Book)-[:IN_CATEGORY]->(c:Category {name: $cat})
USING JOIN ON b
RETURN a.name, b.title

CartesianProduct — Two Unconnected MATCHes

// Bad (Cartesian product):
MATCH (a:Author {id: $aid})
MATCH (b:Book  {id: $bid})
RETURN a.name, b.title

// Good (explicit join or WITH):
MATCH (a:Author {id: $aid})-[:WROTE]->(b:Book {id: $bid})
RETURN a.name, b.title
// Or: WITH between them to reset planning context

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

CYPHER 25
MATCH (p:Person) WHERE p.score > 100
CALL (p) { SET p.tier = 'gold' } IN TRANSACTIONS OF 1000 ROWS

Expensive CONTAINS / ENDS WITH

// Needs TEXT index (RANGE does NOT support these):
CREATE TEXT INDEX person_bio IF NOT EXISTS FOR (n:Person) ON (n.bio)
MATCH (p:Person) WHERE p.bio CONTAINS $keyword RETURN p.name

Over-Traversal — Push LIMIT Early

// Bad: LIMIT after expensive join
MATCH (a:Author)-[:WROTE]->(b:Book)-[:REVIEWED_BY]->(r:Review)
RETURN a.name, b.title, r.text LIMIT 10

// Good: anchor limit before fan-out
MATCH (a:Author)-[:WROTE]->(b:Book)
WITH a, b LIMIT 10
MATCH (b)-[:REVIEWED_BY]->(r:Review)
RETURN a.name, b.title, r.text

---

Cypher Runtime Selection

Pipelined is default for most queries. Parallel requires dbms.cypher.parallel.worker_limit configured; available on Enterprise and Aura Pro 2025+.

// Force parallel for large aggregation:
CYPHER 25 runtime=parallel
MATCH (n:Transaction) WHERE n.amount > 1000
RETURN n.currency, count(*), sum(n.amount)

---

Query Monitoring Commands

// Live queries + resource usage:
SHOW QUERIES YIELD query, queryId, elapsedTimeMillis, allocatedBytes, status, username

// Running transactions:
SHOW TRANSACTIONS YIELD transactionId, currentQuery, currentQueryProgress, elapsedTime, status, username, cpuTime, activeLockCount  // currentQueryProgress added [2026.03]

// Kill a specific transaction:
TERMINATE TRANSACTION $transactionId

// Kill a query:
TERMINATE QUERY $queryId

// Graph count stats (node/rel counts by label/type — feed into planner):
CALL db.stats.retrieve('GRAPH COUNTS') YIELD section, data RETURN section, data

// Token stats (label/property/rel-type IDs):
CALL db.stats.retrieve('TOKENS') YIELD section, data RETURN section, data

Full monitoring reference → 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

Diagnoses and fixes slow Neo4j Cypher queries by interpreting execution plans, identifying bad operators, and prescribing targeted fixes.

What it covers

• EXPLAIN vs PROFILE — when to use each; key metrics (dbHits, rows, estimatedRows, pageCacheHitRatio)
• Execution plan operators — complete reference table with good/bad signals and fix strategies
• Cardinality estimation — detecting stale stats, forcing replanning
• Common plan problems — missing indexes, CartesianProduct, Eager, over-traversal
• Planner hints — USING INDEX, USING SCAN, USING JOIN ON
• Runtime selection — slotted, pipelined, parallel; when each is appropriate
• Query monitoring — SHOW QUERIES, SHOW TRANSACTIONS, TERMINATE TRANSACTION, db.stats.retrieve

Availability

Works with any Neo4j 2025.x / 2026.x instance (self-managed or Aura). Some features require Enterprise edition:

• SHOW QUERIES for other users' queries — Enterprise
• runtime=parallel — Enterprise or Aura Pro 2025+

Install

# Using Claude Code (agentskills.io):
/skill install neo4j-query-tuning-skill

Reference Files

• `references/plan-operators.md` — complete operator table with all variants
• `references/stats-and-monitoring.md` — SHOW QUERIES, SHOW TRANSACTIONS, db.stats.*, index health, page cache

Cypher Execution Plan Operators — Full Reference

Read plans bottom-up: leaf operators at bottom, ProduceResults at top.

Lazy vs Eager: Most operators stream rows to parent as produced. Eager operators (marked ✗ below) must consume all input before emitting output — they materialise the full row set and can cause OOM on large inputs.

---

Leaf Operators (data source)

---

Traversal Operators

---

Join Operators

---

Filter / Projection Operators

---

Aggregation Operators

---

Sort / Limit Operators

---

Write Operators

---

Control / Subquery Operators

---

Reading the Plan: Worked Example

ProduceResults         ← root; read last
  |
  Filter               ← predicate not index-backed
    |
    Expand(All)        ← traversal from matched node
      |
      NodeIndexSeek    ← leaf; read first; index used ✓

Index seek is efficient, expand is normal, filter applied after expand (not index-backed). If filter is selective, add a composite index or move the WHERE earlier.

---

Operator Hints

// Force index:
MATCH (p:Person {email: $email})
USING INDEX p:Person(email)
RETURN p

// Force label scan (ignore index):
MATCH (p:Person {active: true})
USING SCAN p:Person
RETURN p

// Force hash join at specific node:
MATCH (a:Author)-[:WROTE]->(b:Book)<-[:REVIEWED]-(r:Reviewer)
USING JOIN ON b
RETURN a.name, r.name

// Force index for relationship property:
MATCH ()-[t:TRANSFER {txId: $id}]->()
USING INDEX t:TRANSFER(txId)
RETURN t

Multiple hints can be combined in one query.

Stats and Monitoring — Reference

SHOW QUERIES

Lists currently running queries across all databases (admin required for other users' queries).

SHOW QUERIES
YIELD query, queryId, database, username, elapsedTimeMillis, allocatedBytes,
      status, activeLockCount, pageHits, pageFaults, protocol, connectionId
WHERE elapsedTimeMillis > 5000
RETURN queryId, username, elapsedTimeMillis, allocatedBytes, query
ORDER BY elapsedTimeMillis DESC

Key fields:

• queryId — use with TERMINATE QUERY
• elapsedTimeMillis — wall time since query started
• allocatedBytes — heap allocated; high = memory pressure
• status — running, planning, waiting, closing
• activeLockCount — >0 means write transaction holding locks
• pageHits / pageFaults — cache hit/miss counts for this query

Kill a single query:

TERMINATE QUERY "query-id-string"
YIELD queryId, username, message

---

SHOW TRANSACTIONS

Lists all currently open transactions.

SHOW TRANSACTIONS
YIELD transactionId, database, username, currentQuery, elapsedTime,
      status, cpuTime, waitTime, idleTime, activeLockCount,
      pageHits, pageFaults, currentQueryId
WHERE status <> 'Terminated'
RETURN transactionId, username, status, elapsedTime, activeLockCount, currentQuery
ORDER BY elapsedTime DESC

Key fields:

• transactionId — use with TERMINATE TRANSACTION
• status — Running, Blocked, Closing, Terminated
• activeLockCount — transactions blocking others will have high counts
• currentQuery — the Cypher string currently executing (or last executed)
• elapsedTime — duration since transaction opened

Terminate a transaction:

TERMINATE TRANSACTION "neo4j-transaction-123"
YIELD transactionId, username, message

Terminate multiple:

TERMINATE TRANSACTIONS "tx-1", "tx-2"
YIELD transactionId, message

---

Database Statistics

Graph Counts

Node/relationship counts by label and type — the data the planner uses for cardinality estimation.

CALL db.stats.retrieve('GRAPH COUNTS')
YIELD section, data
RETURN section, data

data map includes keys like:

• nodes — total node count
• relationships — total rel count
• nodesByLabel — map of {label: count}
• relsByType — map of {type: count}
• relsByTypeStartingLabel / relsByTypeEndingLabel — selectivity data

Token Stats

CALL db.stats.retrieve('TOKENS')
YIELD section, data
RETURN section, data

Returns internal token ID mappings for labels, property keys, and relationship types.

Retrieve All Stats

CALL db.stats.retrieveAllAnonymized('GRAPH COUNTS')
YIELD section, data
RETURN section, data

Anonymized version for sharing without exposing property names.

---

Statistics and Replanning

Config

dbms.cypher.statistics_divergence_threshold (default: 0.75)

Formula: abs(a - b) / max(a, b). At 0.75, plan invalidated when statistics change by 75% (~4× growth/shrink). Lower to replan more aggressively on growing databases.

Force Replanning

// Recalculate all statistics immediately (blocks until complete):
CALL db.prepareForReplanning()

// Resample a specific index asynchronously:
CALL db.resampleIndex("index-name")

// Resample all outdated indexes asynchronously:
CALL db.resampleOutdatedIndexes()

Force replanning of a single query without changing stats:

CYPHER replan=force
MATCH (p:Person {email: $email}) RETURN p.name

Skip replanning (use cached plan even if stale — useful during high-load bursts):

CYPHER replan=skip
MATCH (p:Person {email: $email}) RETURN p.name

---

Index Health Check

// Indexes not yet ONLINE (still populating or failed):
SHOW INDEXES YIELD name, type, labelsOrTypes, properties, state
WHERE state <> 'ONLINE'
RETURN name, type, labelsOrTypes, properties, state

// All online indexes:
SHOW INDEXES YIELD name, type, labelsOrTypes, properties, state, populationPercent
WHERE state = 'ONLINE'
RETURN name, type, labelsOrTypes, properties
ORDER BY type, labelsOrTypes

Index types and supported predicates:

---

Query Log (server-side)

On self-managed Neo4j, slow queries log to neo4j.log and query.log:

Config options (neo4j.conf):

db.logs.query.enabled=INFO          # Log all queries (verbose) or WARN (slow only)
db.logs.query.threshold=2s          # Log queries taking longer than this
db.logs.query.parameter_logging_enabled=true
db.logs.query.allocation_logging_enabled=true
db.logs.query.page_logging_enabled=true

Each log entry includes: {elapsedMs} ms: {query} with optional params, allocated bytes, page hits/misses.

---

Page Cache Sizing

Small page cache → high pageFaults → disk I/O → slow queries.

// Current page cache stats:
CALL dbms.queryJmx("org.neo4j:instance=kernel#0,name=Page cache")
YIELD attributes
RETURN attributes

Or from SHOW TRANSACTIONS/QUERIES:

• pageHits high, pageFaults low → cache is sufficient
• pageFaults > 1% of pageHits → increase server.memory.pagecache.size in neo4j.conf

Set page cache to hold the entire graph store (graph.db/ directory size).