<p dir="auto">The <a href="https://github.com/neo4j/neo4j/blob/4.2/community/kernel/src/main/java/org/neo4j/kernel/impl/coreapi/TransactionImpl.java#L744-L772">current implementation of <code class="notranslate">TransactionImpl#nodesByLabelAndProperties</code></a> will scan all nodes with a label if there are no indices that include all of the query properties and no other properties. It should be optimized to use partially matching indices (indices which have a subset of the query properties, or which have extra properties, or both), and further filter matches if necessary.</p>
<p dir="auto">This method is used by several methods on Transaction, including <code class="notranslate">findNodes(Label, Map&lt;String, Object&gt;)</code>, <code class="notranslate">findNodes(Label, String, Object)</code>, etc. This can result in significant performance problems when using these methods with a set of properties that does not match an index exactly.</p>
<p dir="auto"><strong>Neo4j Version:</strong> current (applies to at least 4.2.x, 4.1.x)<br>
<strong>Operating System:</strong> all<br>
<strong>API:</strong> Java API</p>
<h3 dir="auto">Steps to reproduce</h3>
<ol dir="auto">
<li>Create an index on a node label: <code class="notranslate">CREATE INDEX FOR (n:Person) ON (n.given_name)</code></li>
<li>Create a large amount of data</li>
<li>Try to find nodes using <code class="notranslate">transaction.findNodes(Label.label("Person"), Map.of("given_name", "foo", "surname", "bar"))</code></li>
</ol>
<h3 dir="auto">Expected behavior</h3>
<p dir="auto">The partially matching index for given_name should be used, resulting in a performant fetch operation</p>
<h3 dir="auto">Actual behavior</h3>
<p dir="auto">The index for given_name will not be used, which results in scanning all nodes (this can be verified by setting a breakpoint on TransactionImpl line 771, where it returns <code class="notranslate">return getNodesByLabelAndPropertyWithoutIndex( labelId, queries );</code>)</p>
<p dir="auto">Hello everybody, I'm having issue with MERGE with Constrains in 2.0.0-M06<br>
Basically, I need to insert buge amount of tweets with relationships between them into Neo4j via REST API's Batch endpoint, approx 5k/s<br>
Sometime, it's a single node creation for a tweet; sometime, I need to create both parent and child nodes first and then create relationship.</p>
<hr>
<p dir="auto">My Schema<br>
[code]<br>
neo4j-sh (0)$ schema<br>
==&gt; Indexes<br>
==&gt;   ON :REPLY(created_at)   ONLINE<br>
==&gt;   ON :REPLY(ids)          ONLINE (for uniqueness constraint)<br>
==&gt;   ON :RETWEET(created_at) ONLINE<br>
==&gt;   ON :RETWEET(ids)        ONLINE (for uniqueness constraint)<br>
==&gt;   ON :Status(userId)      ONLINE<br>
==&gt;   ON :Status(statusId)    ONLINE (for uniqueness constraint)<br>
==&gt;<br>
==&gt; Constraints<br>
==&gt;   ON (status:Status) ASSERT status.statusId IS UNIQUE<br>
==&gt;   ON (retweet:RETWEET) ASSERT retweet.ids IS UNIQUE<br>
==&gt;   ON (reply:REPLY) ASSERT reply.ids IS UNIQUE<br>
[/code]</p>
<hr>
<p dir="auto">ISSUE #ONE: MERGE doesn't ALWAYS work with Constrains properly (or Constrains doesn't work with MERGE properly). (random exception)</p>
<p dir="auto">Here are my test cases:</p>
<ol dir="auto">
<li>Two MERGE's and one CREATE UNIQUE<br>
When I tried to add two tweets with one relationship between them, I got a exception, which complaining existing node for the child<br>
neo4j-sh (0)$ MERGE (parent:Status {statusId:385813052047626240, userId:466042117}) MERGE (child:Status {statusId:385814827270017024, userId:1130050117}) CREATE UNIQUE parent-[relationship:REPLY {ids:'385813052047626240_385814827270017024', created_at:'Thu Oct 03 14:13:17 ADT 2013' }]-&gt;child;<br>
==&gt; CypherExecutionException: Node 270100 already exists with label Status and property "statusId"=[385814827270017024]</li>
<li>Then, I broke down my joint query to small pieces. So first, I tried to create the parent node with a single MERGE, it works as expected<br>
neo4j-sh (0)$ MERGE (parent:Status {statusId:385813052047626240, userId:466042117}) RETURN parent;<br>
==&gt; +------------------------------------------------------------+<br>
==&gt; | parent                                                     |<br>
==&gt; +------------------------------------------------------------+<br>
==&gt; | Node[284599]{userId:466042117,statusId:385813052047626240} |<br>
==&gt; +------------------------------------------------------------+<br>
==&gt; 1 row<br>
==&gt; Nodes created: 1<br>
==&gt; Properties set: 2<br>
==&gt; Labels added: 1<br>
==&gt; 4 ms<br>
neo4j-sh (0)$ MERGE (parent:Status {statusId:385813052047626240, userId:466042117}) RETURN parent;<br>
==&gt; +------------------------------------------------------------+<br>
==&gt; | parent                                                     |<br>
==&gt; +------------------------------------------------------------+<br>
==&gt; | Node[284599]{statusId:385813052047626240,userId:466042117} |<br>
==&gt; +------------------------------------------------------------+<br>
==&gt; 1 row<br>
==&gt; 1 ms</li>
<li>Next, I tried to create the child node with a single MERGE, but it failed like step 1.<br>
neo4j-sh (0)$ MERGE (child:Status {statusId:385814827270017024, userId:1130050117}) RETURN child;<br>
==&gt; CypherExecutionException: Node 270100 already exists with label Status and property "statusId"=[385814827270017024]</li>
<li>I tried MERGE on few more exsting nodes, some worked, some failed.<br>
I thought it may cased by existing relationship, so I checked both direction of a problemtic node, which turns out it doesn't have any existing relationship in place.<br>
neo4j-sh (0)$ MERGE (child:Status {statusId:385814902293553153, userId:1725656978});<br>
==&gt; CypherExecutionException: Node 301777 already exists with label Status and property "statusId"=[385814902293553153]<br>
neo4j-sh (0)$ START a = node(301777) MATCH a-[r]-&gt;b RETURN b;<br>
==&gt; +---+<br>
==&gt; | b |<br>
==&gt; +---+<br>
==&gt; +---+<br>
==&gt; 0 row<br>
==&gt; 1 ms<br>
neo4j-sh (0)$ START a = node(301777) MATCH b-[r]-&gt;a RETURN b;<br>
==&gt; +---+<br>
==&gt; | b |<br>
==&gt; +---+<br>
==&gt; +---+<br>
==&gt; 0 row<br>
==&gt; 0 ms</li>
</ol>
<hr>
<p dir="auto">ISSUE #TWO: Constrained property on relationship doesn't work with CREATE UNIQUE properly, I was able to create duplicate reltionships with same constrained property.</p>
<ol dir="auto">
<li>First, I have created two nodes and one relationship between them. (MERGE with Constrains worked this time :)), the results below is to show I have them in DB<br>
neo4j-sh (0)$ MERGE (parent:Status {statusId:1001, userId:901}) MERGE (child:Status {statusId:1002, userId:902}) CREATE UNIQUE parent-[relationship:RETWEET {ids:'1001_1002', created_at:'Mon Nov 04 14:37:56 AST 2013' }]-&gt;child RETURN parent, relationship, child;<br>
==&gt; +------------------------------------------------------------------------------------------------------------------------------------------------+<br>
==&gt; | parent                            | relationship                                                           | child                             |<br>
==&gt; +------------------------------------------------------------------------------------------------------------------------------------------------+<br>
==&gt; | Node[1]{statusId:1001,userId:901} | :RETWEET[0]{ids:"1001_1002",created_at:"Mon Nov 04 14:37:56 AST 2013"} | Node[2]{statusId:1002,userId:902} |<br>
==&gt; +------------------------------------------------------------------------------------------------------------------------------------------------+<br>
==&gt; 1 row<br>
==&gt; 13 ms</li>
<li>Then, I modified the script a bit, where I changed year in created_at from 2013 to 2019, and I expect this to fail, coz the unique constrain is on ids, but the script created a new relationship between same nodes, so it ends up I have two relationships with same constrained ids between two same nodes.<br>
neo4j-sh (0)$ MERGE (parent:Status {statusId:1001, userId:901}) MERGE (child:Status {statusId:1002, userId:902}) CREATE UNIQUE parent-[relationship:RETWEET {ids:'1001_1002', created_at:'Mon Nov 04 14:37:56 AST 2019' }]-&gt;child RETURN parent, relationship, child;<br>
==&gt; +-----------------------------------------------------------------------------------------------------------------------------------------------------+<br>
==&gt; | parent                            | relationship                                                                | child                             |<br>
==&gt; +-----------------------------------------------------------------------------------------------------------------------------------------------------+<br>
==&gt; | Node[1]{statusId:1001,userId:901} | :RETWEET[224428]{ids:"1001_1002",created_at:"Mon Nov 04 14:37:56 AST 2019"} | Node[2]{statusId:1002,userId:902} |<br>
==&gt; +-----------------------------------------------------------------------------------------------------------------------------------------------------+<br>
==&gt; 1 row<br>
==&gt; Relationships created: 1<br>
==&gt; Properties set: 2<br>
==&gt; 12 ms<br>
neo4j-sh (0)$ START parent=node(1) MATCH parent-[relationship]-&gt;(child) WHERE child.statusId=1002 RETURN parent,relationship,child;<br>
==&gt; +-----------------------------------------------------------------------------------------------------------------------------------------------------+<br>
==&gt; | parent                            | relationship                                                                | child                             |<br>
==&gt; +-----------------------------------------------------------------------------------------------------------------------------------------------------+<br>
==&gt; | Node[1]{statusId:1001,userId:901} | :RETWEET[0]{ids:"1001_1002",created_at:"Mon Nov 04 14:37:56 AST 2013"}      | Node[2]{statusId:1002,userId:902} |<br>
==&gt; | Node[1]{statusId:1001,userId:901} | :RETWEET[224428]{ids:"1001_1002",created_at:"Mon Nov 04 14:37:56 AST 2019"} | Node[2]{statusId:1002,userId:902} |<br>
==&gt; +-----------------------------------------------------------------------------------------------------------------------------------------------------+<br>
==&gt; 2 rows<br>
==&gt; 7 ms</li>
</ol>
<p dir="auto">Any help would be much appreciated!!!!! Thank you!</p>
<ul dir="auto">
<li>Jack</li>
</ul>