# Query taking unusually long to complete **URL:** \ **Category:** Neo4j Graph Platform\ **Tags:** performance, cypher\ **Created:** [July 17, 2020, 12:26pm UTC](https://community.neo4j.com/t/query-taking-unusually-long-to-complete/21823 "2020-07-17T12:26:05Z")\ **Posts on this page:** 17\ **Page:** 1
**Author:** ![gavvalrohit21](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/gavvalrohit21/32/3873_2.png) [@gavvalrohit21](https://community.neo4j.com/u/gavvalrohit21)\ **Post date:** [July 17, 2020, 12:26pm UTC](https://community.neo4j.com/t/query-taking-unusually-long-to-complete/21823/1 "2020-07-17T12:26:05Z")
I have a Neo4j 4.1.0 community edition setup on an EC2 instance (Ubuntu 18.04) with 16 GB RAM. The size of the database is 211 M, determined by running `du -hs /var/lib/neo4j/data/databases/neo4j/` which is made up of about 93K nodes of 3 labels with a single property each. I have configured the following settings as suggested by `neo4j-admin memrec`. ```auto dbms.memory.heap.initial_size=6g dbms.memory.heap.max_size=6g dbms.memory.pagecache.size=7g ``` I am running the following query which is taking about 6 minutes to get completed. `MATCH (person:`Person`), (person:`Person`)-[r0:`STUDIED\_AT`]-(college:`College`), (college:`College`)-[r]-(x) RETURN type(r) AS label, last(labels(x)) AS target, count(r) AS count ORDER BY count(r) DESC` Can someone help me understand why this query is taking so long to run although the size of the graph is pretty small and the system specs are good enough? Also, is there a way to speed up the execution considerably without modifying the query (because the query is coming from popoto.js and I do not have much control over it). I have already tried the following: 1. CALL apoc.warmup.run() 2. Run the same query twice (expecting a better time at second execution) 3. Create index on all three labels (I do not need to write to the DB, it is largely read-only). Couple of more questions: 1. What limits the size/number of requests to the DB? How can I accommodate more? 2. Is caching results possible? I know that neo4j caches the db and the query plans but not sure if results can be cached. I saw a feature request in the github issues but not sure if it got addressed. ---
**Author:** ![webtic](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/webtic/32/7612_2.png) [@webtic](https://community.neo4j.com/u/webtic)\ **Post date:** [July 17, 2020, 1:01pm UTC](https://community.neo4j.com/t/query-taking-unusually-long-to-complete/21823/2 "2020-07-17T13:01:34Z")
Can you post the output of the query with prepended with the keyword EXPLAIN ? This shows the processing done for the query and gives more insight. See [https://neo4j.com/docs/cypher-manual/current/query-tuning/how-do-i-profile-a-query/](https://neo4j.com/docs/cypher-manual/current/query-tuning/how-do-i-profile-a-query/) for more info ---
**Author:** ![gavvalrohit21](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/gavvalrohit21/32/3873_2.png) [@gavvalrohit21](https://community.neo4j.com/u/gavvalrohit21)\ **Post date:** [July 17, 2020, 1:09pm UTC](https://community.neo4j.com/t/query-taking-unusually-long-to-complete/21823/3 "2020-07-17T13:09:43Z")
![plan](https://us1.discourse-cdn.com/flex021/uploads/neo4jcommunity/original/2X/f/f531f6bd348bee91a2021a9990cc1c5b95335ba0.png) Here is the output of EXPLAIN. Please let me know if you need more details. ---
**Author:** ![webtic](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/webtic/32/7612_2.png) [@webtic](https://community.neo4j.com/u/webtic)\ **Post date:** [July 17, 2020, 1:28pm UTC](https://community.neo4j.com/t/query-taking-unusually-long-to-complete/21823/4 "2020-07-17T13:28:35Z")
You probably can rewrite it to which avoids some cartesian duplication: > [@gavvalrohit21](#): > > `MATCH (person:` Person `)-[r0:` STUDIED\_AT `]-(college:` College `)-[r]-(x) RETURN type(r) AS label, last(labels(x)) AS target, count(r) AS count ORDER BY count(r) DESC` ---
**Author:** ![gavvalrohit21](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/gavvalrohit21/32/3873_2.png) [@gavvalrohit21](https://community.neo4j.com/u/gavvalrohit21)\ **Post date:** [July 17, 2020, 1:37pm UTC](https://community.neo4j.com/t/query-taking-unusually-long-to-complete/21823/5 "2020-07-17T13:37:30Z")
Unfortunately I can't edit the query. It's created internally by a js library which I am using for my application. So firstly I am trying to assess if this performance (given the size of the data and the machine configuration) is warranted and if there is a way to configure neo4j for faster performance ---
**Author:** ![webtic](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/webtic/32/7612_2.png) [@webtic](https://community.neo4j.com/u/webtic)\ **Post date:** [July 17, 2020, 1:45pm UTC](https://community.neo4j.com/t/query-taking-unusually-long-to-complete/21823/6 "2020-07-17T13:45:42Z")
What js library is that? Even if you can't change the generated code it is interesting to know how it compares to the generated query. ---
**Author:** ![gavvalrohit21](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/gavvalrohit21/32/3873_2.png) [@gavvalrohit21](https://community.neo4j.com/u/gavvalrohit21)\ **Post date:** [July 17, 2020, 1:48pm UTC](https://community.neo4j.com/t/query-taking-unusually-long-to-complete/21823/7 "2020-07-17T13:48:40Z")
The library is popoto.js ---
**Author:** ![webtic](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/webtic/32/7612_2.png) [@webtic](https://community.neo4j.com/u/webtic)\ **Post date:** [July 17, 2020, 1:59pm UTC](https://community.neo4j.com/t/query-taking-unusually-long-to-complete/21823/8 "2020-07-17T13:59:25Z")
Sorry not familiar with it, perhaps others are :) ---
**Author:** ![gavvalrohit21](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/gavvalrohit21/32/3873_2.png) [@gavvalrohit21](https://community.neo4j.com/u/gavvalrohit21)\ **Post date:** [July 17, 2020, 2:09pm UTC](https://community.neo4j.com/t/query-taking-unusually-long-to-complete/21823/9 "2020-07-17T14:09:11Z")
Thanks for trying to help. Would you be able to comment on whether this performance (given the size of the data and the machine configuration) is warranted? ---
**Author:** ![webtic](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/webtic/32/7612_2.png) [@webtic](https://community.neo4j.com/u/webtic)\ **Post date:** [July 17, 2020, 2:17pm UTC](https://community.neo4j.com/t/query-taking-unusually-long-to-complete/21823/10 "2020-07-17T14:17:58Z")
6 minutes seems outrageous long, which instance type are you using? I would love to see how much time is shaved off with the query rewrite. Even if you can't "fix" the query its good to know if this helps. Are you able to download the dataset and try it on a local Neo4J desktop instance? Just to see how it compares to the EC2 instance.. ---
**Author:** ![webtic](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/webtic/32/7612_2.png) [@webtic](https://community.neo4j.com/u/webtic)\ **Post date:** [July 17, 2020, 2:19pm UTC](https://community.neo4j.com/t/query-taking-unusually-long-to-complete/21823/11 "2020-07-17T14:19:47Z")
you might want to post the output of PROFILE as well, just to get a bit more insight. ---
**Author:** ![gavvalrohit21](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/gavvalrohit21/32/3873_2.png) [@gavvalrohit21](https://community.neo4j.com/u/gavvalrohit21)\ **Post date:** [July 17, 2020, 3:03pm UTC](https://community.neo4j.com/t/query-taking-unusually-long-to-complete/21823/12 "2020-07-17T15:03:21Z")
Here you go, thanks for looking ![plan](https://us1.discourse-cdn.com/flex021/uploads/neo4jcommunity/original/2X/e/e44e49f41e93ca2d088cdf66c368353957055377.png) ---
**Author:** ![gavvalrohit21](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/gavvalrohit21/32/3873_2.png) [@gavvalrohit21](https://community.neo4j.com/u/gavvalrohit21)\ **Post date:** [July 17, 2020, 3:04pm UTC](https://community.neo4j.com/t/query-taking-unusually-long-to-complete/21823/13 "2020-07-17T15:04:30Z")
I'm not sure if it is zoomable. Here is the link to the image in case it is not. > **[plan hosted at ImgBB](https://ibb.co/P1NS9DP)** > > Image plan hosted in ImgBB ---
**Author:** ![webtic](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/webtic/32/7612_2.png) [@webtic](https://community.neo4j.com/u/webtic)\ **Post date:** [July 22, 2020, 3:06pm UTC](https://community.neo4j.com/t/query-taking-unusually-long-to-complete/21823/14 "2020-07-22T15:06:12Z")
As you can notice the query causes an enormous cartesian product, this is why its so slow. What is it you are trying to build? I would investigate in getting popoto to be smarter with the query or move away from popoto. ---
**Author:** ![gavvalrohit21](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/gavvalrohit21/32/3873_2.png) [@gavvalrohit21](https://community.neo4j.com/u/gavvalrohit21)\ **Post date:** [July 24, 2020, 12:45pm UTC](https://community.neo4j.com/t/query-taking-unusually-long-to-complete/21823/15 "2020-07-24T12:45:35Z")
Thanks. I am trying to build a web interface for neo4j to make a dataset available for users to explore. I figured out a way to edit the queries created by popoto on the server side. That resolved the issue. Thanks for looking into this. ---
**Author:** ![webtic](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/webtic/32/7612_2.png) [@webtic](https://community.neo4j.com/u/webtic)\ **Post date:** [July 24, 2020, 1:04pm UTC](https://community.neo4j.com/t/query-taking-unusually-long-to-complete/21823/16 "2020-07-24T13:04:25Z")
Great thanks for the update, appreciated! ---
**Author:** ![v.phanimadhavi85](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/v.phanimadhavi85/32/17757_2.png) [@v.phanimadhavi85](https://community.neo4j.com/u/v.phanimadhavi85)\ **Post date:** [July 14, 2021, 4:04pm UTC](https://community.neo4j.com/t/query-taking-unusually-long-to-complete/21823/17 "2021-07-14T16:04:42Z")
Hi, Could you please share the way to edit queries created by popoto on server side. Also I want to know how we can write custom queries in popoto js . I am trying to do it with help of schema. But your help means a lot to me. Thanks in advance.