Database has record event, that points to a lot of nodes with label state.
It is possible to have a lot of event nodes, each with their own state
nodes. Each State node has time property on it, that represents timestamp.
I am trying to come up with the most efficient query to do so. So far this
is the best I can do:
profile match (:Event {id:'test'})-->(s:State)
with (s)
where s.time > 1524842390
return s;
This is what I get:
+------------------+----------------+------+---------+-----------+--------------------------------+----------------------------------------+
| Operator | Estimated Rows | Rows | DB Hits | Cache H/M |
Identifiers | Other |
+------------------+----------------+------+---------+-----------+--------------------------------+----------------------------------------+
| +ProduceResults | 1080 | 2323 | 0 | 0/0 |
anon[61], anon[27], anon[7], s | 0.0 |
| |
+----------------+------+---------+-----------+--------------------------------+----------------------------------------+
| +Filter | 1080 | 2323 | 0 | 0/0 |
anon[61], anon[27], anon[7], s | 0.0; ReferenceFromSlot(0) |
| |
+----------------+------+---------+-----------+--------------------------------+----------------------------------------+
| +Projection | 1440 | 7200 | 7200 | 0/0 |
anon[61], anon[27], anon[7], s | 0.0; {s : s, : s.time > { AUTOINT1}} |
| |
+----------------+------+---------+-----------+--------------------------------+----------------------------------------+
| +Filter | 1440 | 7200 | 7200 | 0/0 |
anon[27], anon[7], s | 0.0; s:State |
| |
+----------------+------+---------+-----------+--------------------------------+----------------------------------------+
| +Expand(All) | 1440 | 7200 | 7201 | 0/0 |
anon[27], anon[7], s | 0.0; (anon[7])-->(s) |
| |
+----------------+------+---------+-----------+--------------------------------+----------------------------------------+
| +Filter | 0 | 1 | 5 | 0/0 | anon[7]
| 0.0; anon[7].id = { AUTOSTRING0} |
| |
+----------------+------+---------+-----------+--------------------------------+----------------------------------------+
| +NodeByLabelScan | 2 | 5 | 6 | 2/0 | anon[7]
| 1.0; :Event |
+------------------+----------------+------+---------+-----------+--------------------------------+----------------------------------------+
Question:
1) Is there any way to optimize away the Filter (7200), Projection (7200)
part, and why is it even there?
2) Is there a more efficient way to do this? I have tried indexing time
attribute on State object, however, as the number of states records in the
database growth, the number of records scanned.
3) Ideally what I want to do is match event, get all state nodes connected
to it, and then use indexed property time on to filter out the records. I
have been trying to get that to work using 'USING' keyword with something
like this:
profile match (:Event {id:'test'})-->(s:State)
with (s)
using index s:State(time)
where s.time > 1524842390
return s
but it does not work
I know some people will suggest using just match with index, here it is and
where is what I get:
profile match (:Event {id:'test'})-->(s:State)
using index s:State(time)
where s.time > 1524842390
return s
+-------------------------+----------------+-------+---------+-----------+----------------------+---------------------------------------------------+
| Operator | Estimated Rows | Rows | DB Hits | Cache H/M |
Identifiers | Other |
+-------------------------+----------------+-------+---------+-----------+----------------------+---------------------------------------------------+
| +ProduceResults | 108 | 2323 | 0 | 40/0 |
anon[27], anon[7], s | 1.0 |
| |
+----------------+-------+---------+-----------+----------------------+---------------------------------------------------+
| +Expand(Into) | 108 | 2323 | 46816 | 40/0 |
anon[27], anon[7], s | 1.0; (anon[7])-->(s) |
| |
+----------------+-------+---------+-----------+----------------------+---------------------------------------------------+
| +CartesianProduct | 540 | 11704 | 0 | 42/0 |
anon[7], s | 1.0 |
| |\
+----------------+-------+---------+-----------+----------------------+---------------------------------------------------+
| | +NodeIndexSeekByRange | 1080 | 11704 | 11705 | 40/0 | s
| 1.0; :State(time) > Parameter( AUTOINT1,Integer) |
| |
+----------------+-------+---------+-----------+----------------------+---------------------------------------------------+
| +Filter | 0 | 1 | 5 | 40/0 |
anon[7] | 1.0; anon[7].id = { AUTOSTRING0} |
| |
+----------------+-------+---------+-----------+----------------------+---------------------------------------------------+
| +NodeByLabelScan | 5 | 5 | 6 | 42/0 |
anon[7] | 1.0; :Event |
+-------------------------+----------------+-------+---------+-----------+----------------------+---------------------------------------------------+
As you can see this will not scale because the NodeIndexSeekByRange will
keep going up as the number of State records in DB will increase. Thus if I
will add 600k more state nodes, performance will be a lot worse.
Thanks!
--
You received this message because you are subscribed to the Google Groups
"Neo4j" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to neo4j+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
