I'm testing upgrading neo4j from 3.0.4 to 3.4.7 and a query that previously returned in 321 ms now hangs and never returns.
I don't use any indexes or constraints.
The explain looks a bit different, but pretty similar
I can't post a profile because I've been waiting for about 3 hours for it to return....
It's a small install with about 50k records.
The data is genealogy data, so relationship heavy. The variable length relationship part of the query is what slows it down in general and in this instance can have a length of up to 18.
I did a pretty minimal vanilla upgrade, I only had to change the upgrade and shell parameters in neo4j.conf to make it work.
Any ideas?
3.0.4 Explain
neo4j-sh (?)$ explain match (c:Person)-[:IS_WHAKAPAPA_FOR]->(tenant:Tenant)
> where tenant.id = 'TRONR' and not((:Person)-[:HAS_PARENT]->(c))
> match (t:Person)-[:IS_WHAKAPAPA_FOR]->(tenant)
> where t.tupunaId is not null
> with c, tenant, t
> match (c)-[:HAS_PARENT*]->(t)
> return count(c);
+--------------------------------------------+
| No data returned, and nothing was changed. |
+--------------------------------------------+
582 ms
Compiler CYPHER 3.0
Planner COST
Runtime INTERPRETED
+-----------------------+----------------+------------------------------------------------+-----------------------------------+
| Operator | Estimated Rows | Variables | Other |
+-----------------------+----------------+------------------------------------------------+-----------------------------------+
| +ProduceResults | 13 | count(c) | count(c) |
| | +----------------+------------------------------------------------+-----------------------------------+
| +EagerAggregation | 13 | count(c) | |
| | +----------------+------------------------------------------------+-----------------------------------+
| +Expand(Into) | 168 | anon[17] -- anon[135], anon[222], c, t, tenant | (c)-[:IS_WHAKAPAPA_FOR]->(tenant) |
| | +----------------+------------------------------------------------+-----------------------------------+
| +AntiSemiApply | 534 | anon[135], anon[222], t, tenant -- c | |
| |\ +----------------+------------------------------------------------+-----------------------------------+
| | +Filter | 2835 | anon[89], anon[98], c | anon[89]:Person |
| | | +----------------+------------------------------------------------+-----------------------------------+
| | +Expand(All) | 2835 | anon[89], anon[98] -- c | (c)<-[:HAS_PARENT]-() |
| | | +----------------+------------------------------------------------+-----------------------------------+
| | +Argument | 2134 | c | |
| | +----------------+------------------------------------------------+-----------------------------------+
| +Filter | 2134 | anon[135], anon[222], c, t, tenant | c:Person |
| | +----------------+------------------------------------------------+-----------------------------------+
| +VarLengthExpand(All) | 2134 | anon[222], c -- anon[135], t, tenant | (t)-[:HAS_PARENT*]->(c) |
| | +----------------+------------------------------------------------+-----------------------------------+
| +Filter | 1607 | anon[135], t, tenant | t:Person AND hasProp(t.tupunaId) |
| | +----------------+------------------------------------------------+-----------------------------------+
| +Expand(All) | 3213 | anon[135], t -- tenant | (tenant)<-[:IS_WHAKAPAPA_FOR]-(t) |
| | +----------------+------------------------------------------------+-----------------------------------+
| +Filter | 0 | tenant | tenant.id == { AUTOSTRING0} |
| | +----------------+------------------------------------------------+-----------------------------------+
| +NodeByLabelScan | 3 | tenant | :Tenant |
+-----------------------+----------------+------------------------------------------------+-----------------------------------+
Total database accesses: ?
3.4.7 Explain
neo4j-sh (?)$ explain match (c:Person)-[:IS_WHAKAPAPA_FOR]->(tenant:Tenant)
> where tenant.id = 'TRONR' and not((:Person)-[:HAS_PARENT]->(c))
> match (t:Person)-[:IS_WHAKAPAPA_FOR]->(tenant)
> where t.tupunaId is not null
> with c, tenant, t
> match (c)-[:HAS_PARENT*]->(t)
> return count(c);
+--------------------------------------------+
| No data returned, and nothing was changed. |
+--------------------------------------------+
2 ms
Compiler CYPHER 3.4
Planner COST
Runtime INTERPRETED
Runtime version 3.4
+-----------------------+----------------+------------------------------------------------+--------------------------------------+
| Operator | Estimated Rows | Variables | Other |
+-----------------------+----------------+------------------------------------------------+--------------------------------------+
| +ProduceResults | 13 | count(c) | |
| | +----------------+------------------------------------------------+--------------------------------------+
| +EagerAggregation | 13 | count(c) | |
| | +----------------+------------------------------------------------+--------------------------------------+
| +Expand(Into) | 168 | anon[135] -- anon[17], anon[222], c, t, tenant | (t)-[:IS_WHAKAPAPA_FOR]->(tenant) |
| | +----------------+------------------------------------------------+--------------------------------------+
| +Filter | 534 | anon[17], anon[222], c, t, tenant | EXISTS(t.tupunaId); t:Person |
| | +----------------+------------------------------------------------+--------------------------------------+
| +VarLengthExpand(All) | 1067 | anon[222], t -- anon[17], c, tenant | (c)-[:HAS_PARENT*]->(t) |
| | +----------------+------------------------------------------------+--------------------------------------+
| +AntiSemiApply | 803 | anon[17], tenant -- c | |
| |\ +----------------+------------------------------------------------+--------------------------------------+
| | +Filter | 4268 | NODE89, REL98, c | ` NODE89`:Person |
| | | +----------------+------------------------------------------------+--------------------------------------+
| | +Expand(All) | 4268 | NODE89, REL98 -- c | (c)<-[ REL98:HAS_PARENT]-( NODE89) |
| | | +----------------+------------------------------------------------+--------------------------------------+
| | +Argument | 3213 | c | |
| | +----------------+------------------------------------------------+--------------------------------------+
| +Filter | 3213 | anon[17], c, tenant | c:Person |
| | +----------------+------------------------------------------------+--------------------------------------+
| +Expand(All) | 3213 | anon[17], c -- tenant | (tenant)<-[:IS_WHAKAPAPA_FOR]-(c) |
| | +----------------+------------------------------------------------+--------------------------------------+
| +Filter | 0 | tenant | tenant.id = $` AUTOSTRING0` |
| | +----------------+------------------------------------------------+--------------------------------------+
| +NodeByLabelScan | 3 | tenant | :Tenant |
+-----------------------+----------------+------------------------------------------------+--------------------------------------+
Total database accesses: ?