neo4j-query-tuning-skill

To fix a slow Cypher query, analyze PROFILE output for high dbHits and scan operators like AllNodesScan or NodeByLabelScan, then apply targeted changes such as adding indexes, using USING INDEX hints, or pushing LIMIT earlier to reduce IO.

EXPLAIN generates the execution plan without running the query to validate structural choices, while PROFILE executes the query and returns concrete metrics like estimatedRows, dbHits, and pageCacheHitRatio to reveal whether the plan matches reality.

To resolve stale cardinality estimation, trigger replanning or index resampling when estimatedRows diverge significantly from actual rows, ensuring the query planner selects optimal operators based on accurate data statistics.

Use USING INDEX or USING SCAN hints when the planner selects inefficient operators like CartesianProduct or Eager, forcing better join strategies and selectivity to improve overall query performance.

Monitor and terminate problematic live Neo4j queries by using SHOW QUERIES and SHOW TRANSACTIONS to identify long-running transactions, then terminating them while leveraging db.stats.retrieve for performance diagnostics.

Select slotted, pipelined, or parallel runtime based on query characteristics and execution plan operators to optimize throughput and reduce IO for slow Cypher queries.