Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 30 ยท Graph Algorithms and Applications

Query performance: indexes, EXPLAIN and PROFILE

Speed up Cypher queries: range and text indexes, reading EXPLAIN and PROFILE plans, counting db hits, avoiding cartesian products and using parameters.

A graph traversal is cheap once it has a starting point. Most slow Cypher queries are slow for one of two reasons: the database had to search a huge number of nodes to find where to start, or the query shape made it build far more rows than the answer needs. Both are fixed by understanding what the query planner does and by reading the plan.

This lesson covers the indexes you will use most (range and text), the two plan-inspection commands EXPLAIN and PROFILE, the idea of db hits, the cartesian-product trap and why you should send parameters instead of building query strings. A real Neo4j server cannot run on this site, so the Cypher and plan output below are read-only, and the plans are simplified and illustrative: operator names and numbers vary between versions and data sets. The JavaScript stand-ins implement a miniature execution engine that counts "hits" so that you can see the ideas as numbers.

Why indexes matter in a graph database

Index-free adjacency (see Neo4j and the property graph model) makes hops cheap, but it does not tell the database which node to start from. For MATCH (p:Person {email: '[email protected]'}) with no index, Neo4j scans every Person node and checks the property. With an index, it jumps to the matching node directly. The search is then followed by traversal from that anchor.

CYPHER
// the default index type: equality, ranges, STARTS WITH, existence checks
CREATE INDEX person_email IF NOT EXISTS
FOR (p:Person) ON (p.email)

// a text index: designed for string predicates such as CONTAINS and ENDS WITH
CREATE TEXT INDEX person_name_text IF NOT EXISTS
FOR (p:Person) ON (p.name)

// a composite index over several properties of one label
CREATE INDEX person_city_born IF NOT EXISTS
FOR (p:Person) ON (p.city, p.born)

SHOW INDEXES

A plain CREATE INDEX makes a range index (the default and most general kind). It supports equality, comparisons like p.born > 1990, and STARTS WITH. A text index is built for string matching and helps CONTAINS and ENDS WITH, which a range index cannot serve efficiently. Other kinds exist as well (for example point indexes for spatial data and full-text indexes for relevance search), so check the manual for your version. A uniqueness constraint (see Cypher: MERGE, SET, DELETE and constraints) also creates a backing index, so you do not need a second one for the same property.

Indexes are not free. Each one takes disk and memory, and every write that touches an indexed property must update it. Create indexes for the properties you use to find starting points, not for every property.

EXPLAIN and PROFILE

Put one of two keywords in front of any query:

  • EXPLAIN shows the plan the planner chose without running the query. It is safe for slow or destructive statements and shows estimated row counts.
  • PROFILE runs the query and shows the plan with real row counts and db hits for each operator.
CYPHER
PROFILE
MATCH (p:Person {email: $email})-[:FOLLOWS]->(f:Person)
RETURN f.name

A plan is read from the bottom up: the lowest operator produces rows, and each operator above it transforms them. A simplified, illustrative profile looks like this:

TEXT
+-----------------+------+---------+
| Operator        | Rows | DB Hits |
+-----------------+------+---------+
| ProduceResults  |    3 |       0 |
| Projection      |    3 |       3 |
| Filter          |    3 |       3 |
| Expand(All)     |    3 |       6 |
| NodeIndexSeek   |    1 |       2 |
+-----------------+------+---------+

A db hit is an abstract unit of storage-engine work, such as reading a node, a relationship or a property, or doing an index lookup. It is not a time measurement, but it is a reliable way to compare two versions of the same query: fewer hits for the same result usually means a faster query. Watch for these signs:

  • NodeByLabelScan or AllNodesScan on a large label when you expected to find one node. Add an index on the property you filter by.
  • A big gap between rows at the bottom and rows at the top: you read lots of data and then throw most of it away. Filter earlier or anchor on a more selective node.
  • Eager operators, which force a stage to finish before the next starts and use memory.
  • CartesianProduct operators, which we cover next.

If you profile on a tiny development database, everything looks fast. Judge plans on data of realistic size, or at least by their shape.

flowchart BT
    A["NodeIndexSeek<br/>Person.email = $email"] --> B["Expand(All)<br/>-[:FOLLOWS]-> (f)"]
    B --> C["Filter<br/>f:Person"]
    C --> D["Projection<br/>f.name"]
    D --> E["ProduceResults"]

A plan is read from the bottom: an index seek finds one node, expansion follows its relationships, and later operators filter and project.

The first stand-in: scan or seek, counted in hits

The program below builds 20,000 people. It runs the same question twice, "who does the person with this email follow?", once with a label scan and once with an index seek. A tiny Profile object counts db hits per operator, using a simplified rule: reading a node or a property costs 1 hit, an index lookup costs 1 hit, and reading a relationship costs 1 hit.

JS Label scan versus index seek

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Run it and the scan plan costs 40,009 hits against 11 with the index. The index makes the start of the query almost free. The expansion and projection cost the same in both plans, since they depend on how many friends the person has, not on how many people exist. This is the pattern to remember: an index fixes the anchor, and the neighbourhood sets the rest.

The cartesian product trap

If a MATCH contains two patterns that share no variable, the database must combine every row of the first with every row of the second.

CYPHER
// risky: 300 people x 50 companies = 15,000 rows before any filtering
MATCH (p:Person), (c:Company)
WHERE p.city = c.city
RETURN p.name, c.name

// better: connect the two with a relationship in the pattern
MATCH (p:Person)-[:WORKS_AT]->(c:Company)
RETURN p.name, c.name

Disconnected patterns are fine when each one is anchored on a single node by an index (for example to create a relationship between two known nodes), because a product of 1 by 1 is 1. They are dangerous when either side can return many rows. Neo4j warns about cartesian products in the plan, and EXPLAIN shows a CartesianProduct operator. If you find one by accident, look for the missing relationship, or split the query with WITH.

Use parameters

A query that builds its text by gluing values together looks like a new query every time. Parameters keep the text the same and send the values separately.

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

In a driver you would pass { email: "[email protected]" } next to the query. Benefits: the planner can cache and reuse the plan, which saves planning time for hot queries; and values are never parsed as Cypher, which prevents injection. Never concatenate user input into a query string.

The second stand-in simulates a plan cache keyed by the exact query text. It counts how many times the "planner" has to run when the email is glued into the string, compared with a parameterized query.

JS Plan cache: literals versus parameters

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Real Neo4j has its own query cache with more rules than this toy, but the principle is the same: one text, many executions.

A tuning routine

  1. Run PROFILE on the slow query with realistic data.
  2. Find the operator with the most db hits and ask why it is there.
  3. If it is a scan, create an index on the property you filter by, then profile again.
  4. If rows balloon, reorder or tighten the pattern so that the most selective part comes first and bound the variable-length paths.
  5. Check for a CartesianProduct and replace it with a connected pattern.
  6. Switch to parameters and confirm with PROFILE that the hits dropped.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the difference between EXPLAIN and PROFILE?

    EXPLAIN is safe because it does not execute the query. PROFILE executes it to report actual rows and db hits.
  2. 2. Which index type is the better choice for WHERE p.name CONTAINS 'son'?

    Text indexes are built for string predicates like CONTAINS and ENDS WITH, which a range index does not serve well.
  3. 3. Which sign in a profile suggests a missing index?

    A label scan that reads many nodes and keeps one means the database had no index to jump to the starting node.
  4. 4. Why prefer parameters over gluing values into the query text?

    The query text stays identical, so its plan can be cached, and values are never parsed as Cypher.

Summary

  • Traversals are cheap once anchored; indexes find the anchor. A plain CREATE INDEX makes a range index, and a text index serves string predicates such as CONTAINS.
  • EXPLAIN shows the plan without running it; PROFILE runs it and reports rows and db hits per operator, read from the bottom up.
  • Db hits are a unit of storage work, good for comparing variants of one query, not a clock.
  • Watch for label scans on big labels, large row counts that shrink later, Eager operators and CartesianProduct.
  • Use parameters so plans can be reused and injection is avoided.
  • The stand-ins use a simplified hit-counting model; real plan output and numbers differ.

Next: Graph algorithms: PageRank, communities, shortest paths.