query-performance.adoc

Initial Data Setup

CREATE (f:Foo { id: "IT8faLFf9AU" })
FOREACH (r in range(0,100) | CREATE (f)-[:r1]->(b:Bar), (f)-[:r2]->(b:Bar), (f)-[:r3]->(b:Bar));

Queries

Query 1: 2330 ms (creates a cartesian product)

MATCH
  (f:Foo { id: "IT8faLFf9AU" }),
  (f)-[r1:r1]->(),
  (f)-[r2:r2]->(),
  (f)-[r3:r3]->()
WITH count(distinct r1) as c1, count(distinct r2) as c2, count(distinct r3) as c3
RETURN c1, c2, c3;

Query 2: 7 ms (avoids cartesian product)

MATCH
  (f:Foo { id: "IT8faLFf9AU" }),
  (f)-[rel:r1|r2|r3]->()
WITH collect(rel) as rel
RETURN
  length(filter(r in rel where type(r) = "r1")) as c1,
  length(filter(r in rel where type(r) = "r2")) as c2,
  length(filter(r in rel where type(r) = "r3")) as c3;

Lesson: do as few as necessary, avoid cartesian products if possible

Profiling

neo4j-sh (?)$ PROFILE MATCH
>   (f:Foo { id: "IT8faLFf9AU" }),
>   (f)-[r1:r1]->(),
>   (f)-[r2:r2]->(),
>   (f)-[r3:r3]->()
> WITH count(distinct r1) as c1, count(distinct r2) as c2, count(distinct r3) as c3
> RETURN c1, c2, c3;
+-----------------+
| c1  | c2  | c3  |
+-----------------+
| 101 | 101 | 101 |
+-----------------+
1 row
1488 ms

Compiler CYPHER 2.2

Planner COST

EagerAggregation
  |
  +Expand(All)(0)
    |
    +Expand(All)(1)
      |
      +Expand(All)(2)
        |
        +Filter
          |
          +NodeByLabelScan

+------------------+---------------+---------+---------+---------------------------------------------+-------------------------+
|         Operator | EstimatedRows |    Rows |  DbHits |                                 Identifiers |                   Other |
+------------------+---------------+---------+---------+---------------------------------------------+-------------------------+
| EagerAggregation |           503 |       1 |       0 |                                  c1, c2, c3 |                         |
|   Expand(All)(0) |        253036 | 1030301 | 1040502 | anon[55], anon[74], anon[93], f, r1, r2, r3 |         (f)-[r3:r3]->() |
|   Expand(All)(1) |          4001 |   10201 |   10302 |               anon[55], anon[74], f, r1, r2 |         (f)-[r2:r2]->() |
|   Expand(All)(2) |            63 |     101 |     102 |                             anon[55], f, r1 |         (f)-[r1:r1]->() |
|           Filter |             1 |       1 |       8 |                                           f | f.id == {  AUTOSTRING0} |
|  NodeByLabelScan |             4 |       4 |       5 |                                           f |                    :Foo |
+------------------+---------------+---------+---------+---------------------------------------------+-------------------------+

Total database accesses: 1050919

neo4j-sh (?)$ PROFILE MATCH
>   (f:Foo { id: "IT8faLFf9AU" }),
>   (f)-[rel:r1|r2|r3]->()
> WITH collect(rel) as rel
> RETURN
>   length(filter(r in rel where type(r) = "r1")) as c1,
>   length(filter(r in rel where type(r) = "r2")) as c2,
>   length(filter(r in rel where type(r) = "r3")) as c3;
+-----------------+
| c1  | c2  | c3  |
+-----------------+
| 101 | 101 | 101 |
+-----------------+
1 row
15 ms

Compiler CYPHER 2.2

Planner COST

Projection
  |
  +EagerAggregation
    |
    +Expand(All)
      |
      +Filter
        |
        +NodeByLabelScan

+------------------+---------------+------+--------+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|         Operator | EstimatedRows | Rows | DbHits |      Identifiers |                                                                                                                                                                                                                                                                     Other |
+------------------+---------------+------+--------+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|       Projection |        833435 |    1 |      0 |  rel, c1, c2, c3 | length(FilterFunction(rel@85,  r@112,RelationshipTypeFunction(  r) == {  AUTOSTRING1})); length(FilterFunction(rel@85,  r@167,RelationshipTypeFunction(  r) == {  AUTOSTRING2})); length(FilterFunction(rel@85,  r@222,RelationshipTypeFunction(  r) == {  AUTOSTRING3})) |
| EagerAggregation |            14 |    1 |      0 |              rel |                                                                                                                                                                                                                                                                           |
|      Expand(All) |           189 |  303 |    304 | anon[62], rel, f |                                                                                                                                                                                                                                             (f)-[  rel@46:r1|:r2|:r3]->() |
|           Filter |             1 |    1 |      8 |                f |                                                                                                                                                                                                                                                   f.id == {  AUTOSTRING0} |
|  NodeByLabelScan |             4 |    4 |      5 |                f |                                                                                                                                                                                                                                                                      :Foo |
+------------------+---------------+------+--------+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Total database accesses: 317

More queries

Query 1: 4 ms

MATCH (f:Foo { id: "IT8faLFf9AU" })
OPTIONAL MATCH (f)-[r1:r1]->()
RETURN
  distinct f.id;

Query 2: 1074 ms

MATCH
  (f:Foo { id: "IT8faLFf9AU" }),
  (f)-[r1:r1]->(),
  (f)-[r2:r2]->(),
  (f)-[r3:r3]->()
RETURN count(f);

Query 3: 52 ms

MATCH (f:Foo { id: "IT8faLFf9AU" })
OPTIONAL MATCH (f)-[r1:r1]->()
OPTIONAL MATCH (f)-[r2:r2]->()
RETURN
  distinct f.id;

Query 4: 2473 ms

MATCH (f:Foo { id: "IT8faLFf9AU" })
OPTIONAL MATCH (f)-[r1:r1]->()
OPTIONAL MATCH (f)-[r2:r2]->()
OPTIONAL MATCH (f)-[r3:r3]->()
RETURN
  distinct f.id;

More profiling

neo4j-sh (?)$ PROFILE MATCH
>   (f:Foo { id: "IT8faLFf9AU" }),
>   (f)-[r1:r1]->(),
>   (f)-[r2:r2]->(),
>   (f)-[r3:r3]->()
> RETURN count(f);
+----------+
| count(f) |
+----------+
| 1030301  |
+----------+
1 row
1058 ms

Compiler CYPHER 2.2

Planner COST

EagerAggregation
  |
  +Expand(All)(0)
    |
    +Expand(All)(1)
      |
      +Expand(All)(2)
        |
        +Filter
          |
          +NodeByLabelScan

+------------------+---------------+---------+---------+---------------------------------------------+-------------------------+
|         Operator | EstimatedRows |    Rows |  DbHits |                                 Identifiers |                   Other |
+------------------+---------------+---------+---------+---------------------------------------------+-------------------------+
| EagerAggregation |           503 |       1 |       0 |                                    count(f) |                         |
|   Expand(All)(0) |        253036 | 1030301 | 1040502 | anon[55], anon[74], anon[93], f, r1, r2, r3 |         (f)-[r3:r3]->() |
|   Expand(All)(1) |          4001 |   10201 |   10302 |               anon[55], anon[74], f, r1, r2 |         (f)-[r2:r2]->() |
|   Expand(All)(2) |            63 |     101 |     102 |                             anon[55], f, r1 |         (f)-[r1:r1]->() |
|           Filter |             1 |       1 |       8 |                                           f | f.id == {  AUTOSTRING0} |
|  NodeByLabelScan |             4 |       4 |       5 |                                           f |                    :Foo |
+------------------+---------------+---------+---------+---------------------------------------------+-------------------------+

Total database accesses: 1050919

neo4j-sh (?)$ PROFILE
> MATCH (f:Foo { id: "IT8faLFf9AU" })
> OPTIONAL MATCH (f)-[r1:r1]->()
> OPTIONAL MATCH (f)-[r2:r2]->()
> OPTIONAL MATCH (f)-[r3:r3]->()
> RETURN
>   distinct f.id;
+---------------+
| f.id          |
+---------------+
| "IT8faLFf9AU" |
+---------------+
1 row
2551 ms

Compiler CYPHER 2.2

Planner COST

Distinct
  |
  +NodeOuterHashJoin
    |
    +OptionalExpand(All)(0)
    |  |
    |  +OptionalExpand(All)(1)
    |    |
    |    +Filter
    |      |
    |      +NodeByLabelScan
    |
    +Expand(All)
       |
       +AllNodesScan

+------------------------+---------------+---------+---------+----------------------------------------------+-------------------------+
|               Operator | EstimatedRows |    Rows |  DbHits |                                  Identifiers |                   Other |
+------------------------+---------------+---------+---------+----------------------------------------------+-------------------------+
|               Distinct |       1682983 |       1 | 2060602 |                                         f.id |                    f.id |
|      NodeOuterHashJoin |       1771561 | 1030301 |       0 | anon[127], anon[65], anon[96], f, r1, r2, r3 |                         |
| OptionalExpand(All)(0) |         14641 |   10201 |   10302 |                anon[65], anon[96], f, r1, r2 |         (f)-[r2:r2]->() |
| OptionalExpand(All)(1) |           121 |     101 |     102 |                              anon[65], f, r1 |         (f)-[r1:r1]->() |
|                 Filter |             1 |       1 |       8 |                                            f | f.id == {  AUTOSTRING0} |
|        NodeByLabelScan |             3 |       4 |       5 |                                            f |                    :Foo |
|            Expand(All) |           363 |     253 |   45227 |                             anon[127], f, r3 |         ()<-[r3:r3]-(f) |
|           AllNodesScan |         44920 |   44974 |   44975 |                                    anon[127] |                         |
+------------------------+---------------+---------+---------+----------------------------------------------+-------------------------+

Total database accesses: 2161221