This is basically a 1 hop, but pinned at both ends, and with multiple possible values at each end. Of course, transpiler also cares about all the subclasses of these input identifiers. Notice also that there are a couple of id's on one end and many on the other. So the final query that gets made into cypher basically looks like:
(a few Ids)<-subclass_of-(on)-has_phenotype->(h)-subclass_of->(a lot of id's).
The profile looks like:
So it gets the subclass nodes on and h. It then has a choice: should it take the phenotype edges from the side where there are lots of ids or the side where there are just a few. Surprisingly, it chooses from the side where there are a lot, so then there are a lot of has phenotype edges that have to be pulled.
In this case, the query can be made much faster by setting where you want to join, like this:
USING JOIN on `h`
Then it forces the has_phenotype to come from the small side. If we wanted to make use of this, we'd need to be able to spot the situation: the input query is a single hop, with both ends pinned, and with more nodes on one side than the other. In that case, it MIGHT make sense to add this Join hint.
There are a couple of caveats that need exploration though before anything gets going. First, it's hard to say that it is much faster, because of the way neo4j caches results. You can look at the # of db hits, but I think that a more careful analysis turning off the cache would be called for. Second, and maybe worse? Is that what happens is that from the small side, none of the nodes have a has_phenotype edge. So the whole query gets abandoned at that point (saves a lot of time). But this might be a special case - perhaps if there were even one edge, you'd have to do enough work on the long side that the savings dissappear, or even worse perhaps neo4j's plan would actually be better in those cases for some reason.
So this will probably require some more careful testing before any implementation. Thoughts @YaphetKG ? @EvanDietzMorris ?
Here is a graph that takes minutes in an ubergraph or robokopkg
This is basically a 1 hop, but pinned at both ends, and with multiple possible values at each end. Of course, transpiler also cares about all the subclasses of these input identifiers. Notice also that there are a couple of id's on one end and many on the other. So the final query that gets made into cypher basically looks like:
(a few Ids)<-subclass_of-(on)-has_phenotype->(h)-subclass_of->(a lot of id's).
The profile looks like:
So it gets the subclass nodes
onandh. It then has a choice: should it take the phenotype edges from the side where there are lots of ids or the side where there are just a few. Surprisingly, it chooses from the side where there are a lot, so then there are a lot of has phenotype edges that have to be pulled.In this case, the query can be made much faster by setting where you want to join, like this:
Then it forces the has_phenotype to come from the small side. If we wanted to make use of this, we'd need to be able to spot the situation: the input query is a single hop, with both ends pinned, and with more nodes on one side than the other. In that case, it MIGHT make sense to add this Join hint.
There are a couple of caveats that need exploration though before anything gets going. First, it's hard to say that it is much faster, because of the way neo4j caches results. You can look at the # of db hits, but I think that a more careful analysis turning off the cache would be called for. Second, and maybe worse? Is that what happens is that from the small side, none of the nodes have a has_phenotype edge. So the whole query gets abandoned at that point (saves a lot of time). But this might be a special case - perhaps if there were even one edge, you'd have to do enough work on the long side that the savings dissappear, or even worse perhaps neo4j's plan would actually be better in those cases for some reason.
So this will probably require some more careful testing before any implementation. Thoughts @YaphetKG ? @EvanDietzMorris ?