# Optimizing simple queries for very large graph DB

**URL:** <https://community.neo4j.com/t/optimizing-simple-queries-for-very-large-graph-db/66568>\
**Category:** Cypher\
**Tags:** performance\
**Created:** [February 28, 2024, 8:33pm UTC](https://community.neo4j.com/t/optimizing-simple-queries-for-very-large-graph-db/66568 "2024-02-28T20:33:02Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![cct](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/cct/32/30986_2.png) [@cct](https://community.neo4j.com/u/cct)\
**Post date:** [February 28, 2024, 8:33pm UTC](https://community.neo4j.com/t/optimizing-simple-queries-for-very-large-graph-db/66568/1 "2024-02-28T20:33:03Z")

</div>

We have a very large graph db, with 1.6 billion Nodes and 8.6 billion Relationships, and have been trying to make simple Cypher queries run in non-geologic time.

The most important query we need to optimize is "given a specific node, how many incoming relationships does the node have?" In Cypher, something like:  
MATCH (n:syrup)-[r:syrup\_to\_syrup]-\>(c:syrup {syrup\_id: 'S999'}) return count(r)

I've tried using "limit" but it's not unusual for the c node to have several hundred thousand incoming edges, and the queries stop returning (or at least start to get immeasureably slow) with limits over 250k or so.

Also I looked at the relationship count store (using "size" instead of "count") but that's been deprecated.

So, here are my questions:

1. Would you consider our database an outlier in terms of size? Can you say if it's too big for neo4j?
2. Do you have any suggestions for optimizing this query?
3. Do you know how large queries affect the neo4j clients? My desktop client goes dog slow after a couple queries and I need to restart it. My python client will typically give up the ghost completely.

Looking forward to your suggestions!  
Best,  
-Mike

---

<div class="post-metadata">

**Author:** ![glilienfield](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/glilienfield/32/27534_2.png) [@glilienfield](https://community.neo4j.com/u/glilienfield)\
**Post date:** [February 28, 2024, 10:02pm UTC](https://community.neo4j.com/t/optimizing-simple-queries-for-very-large-graph-db/66568/2 "2024-02-28T22:02:18Z")

</div>

Is it possible to have other types of nodes other than 'syrup' nodes related to a 'syrup\_to\_syrup' node? I doesn't seem so based on your relationship type. If true, true not specifying the label of the target node. This will eliminate it needing to load the other node to check its labels.

```auto
MATCH ()-[r:syrup_to_syrup]->(c:syrup {syrup_id: 'S999'}) 
return count(r)

```

maybe it helps.

---

<div class="post-metadata">

**Author:** ![dana\_canzano](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/dana_canzano/32/27392_2.png) [@dana\_canzano](https://community.neo4j.com/u/dana_canzano)\
**Post date:** [February 28, 2024, 10:05pm UTC](https://community.neo4j.com/t/optimizing-simple-queries-for-very-large-graph-db/66568/3 "2024-02-28T22:05:57Z")

</div>

@cct

a. what version of Neo4j?

b. there is metadata on each node which holds the # of incomin/outgoing relationhips by type and direction.  
For example pre v5 one can run

```auto
match (c:syrup {syrup_id: 'S999'})  
return size ( (c)-[:syrup_to_syrup]->() );

```

and this will report the number of relationships on node :syrup {syrup\_id: 'S999'} and the relationship type is named `:syrup_to_syrup` and the relationship direction is outgoing.

For v5 the equivalent is

```auto
match (c:syrup {syrup_id: 'S999'})  
return count { (c)-[:syrup_to_syrup]->() };

```

note if you add a label to the other side of the expression, i.e.

```auto
match (c:syrup {syrup_id: 'S999'})  
return count { (c)-[:syrup_to_syrup]->(n:syrup) };

```

the metadata is not consulted and thus we need to iterate 1 by 1 for every syrup\_to\_syrup relationship and then check if the destination node has a label named :syrup

---

<div class="post-metadata">

**Author:** ![cct](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/cct/32/30986_2.png) [@cct](https://community.neo4j.com/u/cct)\
**Post date:** [February 28, 2024, 10:08pm UTC](https://community.neo4j.com/t/optimizing-simple-queries-for-very-large-graph-db/66568/4 "2024-02-28T22:08:24Z")

</div>

Thank you for your suggestion. My experience with Cypher queries on our db has been that, in general, queries take a lot longer when you don't specify categories. But I like your logic and will give it a shot. thanks again!

---

<div class="post-metadata">

**Author:** ![cct](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/cct/32/30986_2.png) [@cct](https://community.neo4j.com/u/cct)\
**Post date:** [February 28, 2024, 10:27pm UTC](https://community.neo4j.com/t/optimizing-simple-queries-for-very-large-graph-db/66568/5 "2024-02-28T22:27:19Z")

</div>

Thank you for your reply. We are running neo4j 5.13.0 Enterprise.

I believe you when you say there is metadata on each node for what I'm looking for, but I haven't been able to crack it in a performant fashion. (i.e. I've gone up to limit 250k in queries but larger than that gets to be immeasurably slow.)

I did try the pre v5 size() operator, but was told it was deprecated.

With respect to specifying categories, I would actually prefer not to! But I've found that queries without categories go much more slowly. Ideally, yeah, I'd just want something like:

```auto
MATCH (c:syrup {syrup_id: 'S999'})
RETURN COUNT ( ()-->(c) )

```

or

```auto
MATCH (c:syrup {syrup_id: 'S999'})
RETURN COUNT ( ()-[]->(c) )

```

(Note that I'm looking to query the number **incoming** not outgoing nodes.)

I'll give it a shot. Thanks again for your help!  
-Mike

---

<div class="post-metadata">

**Author:** ![dana\_canzano](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/dana_canzano/32/27392_2.png) [@dana\_canzano](https://community.neo4j.com/u/dana_canzano)\
**Post date:** [February 28, 2024, 11:37pm UTC](https://community.neo4j.com/t/optimizing-simple-queries-for-very-large-graph-db/66568/6 "2024-02-28T23:37:09Z")

</div>

@cct

yes for v5 do not use `size ( (s)-[r] ->() )` instead use `count { (s)-[r] ->() }`

note thats count { } and not count ( ). i.e. for this case its count squiggly brace and not count paren

---

<div class="post-metadata">

**Author:** ![cct](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/cct/32/30986_2.png) [@cct](https://community.neo4j.com/u/cct)\
**Post date:** [February 28, 2024, 11:39pm UTC](https://community.neo4j.com/t/optimizing-simple-queries-for-very-large-graph-db/66568/7 "2024-02-28T23:39:16Z")

</div>

While I was reading you guys' replies, I was also running the following:

```auto
match ()-[r:syrup_to_syrup]->(c:syrup {syrup_id:'S9999'}) return count(r)

```

It finished successfully after about 65 minutes.  
(The answer was 24757502 incoming connections.)

Then I followed dana's suggestion and ran:

```auto
match (c:syrup {syrup_id:'S9999'}) return count { ()-[:syrup_to_syrup]->(c) }

```

and it finished in 288ms!

Of course, the previous results were probably cached. So right now I'm fishing around in the graph for another node with an ultra high number of incoming connections. (The vaaaaast majority of nodes have only single-digit incoming edges.) I'll keep y'all posted.

---

<div class="post-metadata">

**Author:** ![cct](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/cct/32/30986_2.png) [@cct](https://community.neo4j.com/u/cct)\
**Post date:** [February 28, 2024, 11:51pm UTC](https://community.neo4j.com/t/optimizing-simple-queries-for-very-large-graph-db/66568/8 "2024-02-28T23:51:41Z")

</div>

I found a node with 2795842 incoming relationships and ran dana's query on it.  
It took 239107 ms, which is a significant improvement. Thanks, dana!

I'm going to work with my chief DS and our engineers and see if ~5 minutes is fast enough for our purposes. In the meantime, thanks again!

-Mike

---

<div class="post-metadata">

**Author:** ![dana\_canzano](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/dana_canzano/32/27392_2.png) [@dana\_canzano](https://community.neo4j.com/u/dana_canzano)\
**Post date:** [February 29, 2024, 12:23am UTC](https://community.neo4j.com/t/optimizing-simple-queries-for-very-large-graph-db/66568/9 "2024-02-29T00:23:39Z")

</div>

@cct

your comment of

```auto
match (c:syrup {syrup_id:'S9999'}) return count { ()-[:syrup_to_syrup]->(c) }

```

and it finished in 288ms!

Of course, the previous results were probably cached.

We do not cache query results. We cache query plans and data the graph may be recorded in RAM but again its just the graph.

If you preface the query with `profile` and thus

```auto
profile match (c:syrup {syrup_id:'S9999'}) return count { ()-[:syrup_to_syrup]->(c) }

```

what this does is produce the query execution plan and then runs the query.  
The query execution plan will first find the node in question, i.e `c:syrup {syrup_id:'S9999'}) `  
If you do not have an index on :syrup(syrup\_id) then if you have 100k :syrup nodes then it will need to examine each of the 100k :syrup nodes to see which node(s) have a syrup\_id='S99999'. If you have an inde in :syrup(syryp\_id) then its a significant smalller number of nodes to check.  
After if finds the node(s) with syrup\_id='S99999' the next block of the query plan should invoked a GetNodeDegree. this GetNodeDegree is the key that its getting its data from the node metadata

you next post asking if 5 mins is fast enough. Provided you have an index I see no reason why this shouldnt be seconds

---

<div class="post-metadata">

**Author:** ![cct](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/cct/32/30986_2.png) [@cct](https://community.neo4j.com/u/cct)\
**Post date:** [February 29, 2024, 12:38am UTC](https://community.neo4j.com/t/optimizing-simple-queries-for-very-large-graph-db/66568/10 "2024-02-29T00:38:38Z")

</div>

Thanks for the drilldown!

I said ~5 minutes because that's how long it took (239107ms) for the other node with 2,795,842 incoming relationships.

And oh yes we have definitely indexed :syrup(syrup\_id). That was one of our earliest lessons!

Thanks again!  
-Mike

---

<div class="post-metadata">

**Author:** ![dana\_canzano](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/dana_canzano/32/27392_2.png) [@dana\_canzano](https://community.neo4j.com/u/dana_canzano)\
**Post date:** [February 29, 2024, 1:23am UTC](https://community.neo4j.com/t/optimizing-simple-queries-for-very-large-graph-db/66568/11 "2024-02-29T01:23:44Z")

</div>

@cct

for the query that takes 5 minutes, if you preface it with `profile` does the query plan report a block that includes a reference to GetDegree?

Can you share the query plan?

---

<div class="post-metadata">

**Author:** ![cct](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/cct/32/30986_2.png) [@cct](https://community.neo4j.com/u/cct)\
**Post date:** [February 29, 2024, 2:08am UTC](https://community.neo4j.com/t/optimizing-simple-queries-for-very-large-graph-db/66568/12 "2024-02-29T02:08:20Z")

</div>

```auto
Planner COST

Runtime PIPELINED

Runtime version 5.13

Batch size 128

+-----------------+----+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| Operator | Id | Details | Estimated Rows | Rows | DB Hits | Memory (Bytes) | Page Cache Hits/Misses | Time (ms) | Pipeline |
+-----------------+----+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+
| +ProduceResults | 0 | cnt | 1 | 1 | 0 | 0 | | | |
| | +----+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+ | | |
| +Projection | 1 | getDegree((c)<-[:concept_to_concept]-()) + getDegree((c)<-[:concept_to_concept_unlabeled]-()) AS cnt | 1 | 1 | 2 | | | | |
| | +----+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+ | | |
| +NodeIndexSeek | 2 | TEXT INDEX c:concept(id) WHERE id = $autostring_0 | 1 | 1 | 2 | 248 | 828670/587 | 1744.357 | Fused in Pipeline 0 |
+-----------------+----+------------------------------------------------------------------------------------------------------+----------------+------+---------+----------------+------------------------+-----------+---------------------+

Total database accesses: 4, total allocated memory: 312

```

---

<div class="post-metadata">

**Author:** ![dana\_canzano](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/dana_canzano/32/27392_2.png) [@dana\_canzano](https://community.neo4j.com/u/dana_canzano)\
**Post date:** [February 29, 2024, 2:19am UTC](https://community.neo4j.com/t/optimizing-simple-queries-for-very-large-graph-db/66568/13 "2024-02-29T02:19:52Z")

</div>

@cct  
thanks. query plan looks as good as it is going to get.

Except we have moved on from :syrup to :concept and all along i was dreaming of :syrup and 🥞 ;)