JanusGraph / JanusGraph/janusgraph
Elasticsearch index not using order when followed by and()
- Dominant language
- Java
- Stars
- 5.8k
- Forks
- 1.2k
- Avg merge
- 13h 53m
- Merged PRs (30d)
- 6
Description
When using an elasticsearch mixed index on an orderable edge property (Integer) and ordering by it, the order is not used in the index query when it's followed by `and(...)` and the traversal ends up iterating all edges (to order them?) which slows down the query drastically.
In the profile results I see `\_orders=[]`
Interestingly if I just add a `limit(Integer.MAX_VALUE)` (semantically useless) after the `order().by()` steps, it causes the index query to search with order (as should have happened in the first query too) which significantly improves the query performance (82x times faster).
In the profile results I see `\_orders=[ASC(since)]`
**Setup**
- Janusgraph: 0.3.2
- storage-backend: scylla 3.0.8
- index-backend: elasticsearch:5.6
**Steps to Reproduce**
```// Create the schema and index
graph.tx().rollback()
mgmt = graph.openManagement();
develops = mgmt.makeEdgeLabel('develops').multiplicity(MULTI).make()
since = mgmt.makePropertyKey('since').dataType(Integer.class).cardinality(Cardinality.SINGLE).make()
mgmt.buildIndex('by-since-mixed', Edge.class).addKey(since).indexOnly(develops).buildMixedIndex("search")
mgmt.commit()
graph.tx().commit()
ManagementSystem.awaitGraphIndexStatus(graph, 'by-since-mixed').status(SchemaStatus.ENABLED).call()
// Import crew graph
graph.io(graphson()).readGraph("data/tinkerpop-crew.json")
gremlin = g.V().hasLabel('software').has('name', 'gremlin').next()
yearRange = 2000..2010
// Add 1000 persons/edges
for (i in 1..10000) {
g.
addV('person').
property('name', "person$i").
addE('develops').
property('since', yearRange[i%yearRange.size()]).
to(gremlin).
iterate()
}
g.tx().commit()
// Query with limit after and()
g.E().hasLabel('develops').has('since', gt(2005)).order().by('since', incr).and(inV().has('name', 'gremlin')).limit(10).profile()
// transaction cache reset
g.tx().rollback()
// Query with max limit directly after order().by()
g.E().hasLabel('develops').has('since', gt(2005)).order().by('since', incr).limit(Integer.MAX_VALUE).and(inV().has('name', 'gremlin')).limit(10).profile()
```
**Profile Results**
```
gremlin> g.E().hasLabel('develops').has('since', gt(2005)).order().by('since', incr).and(inV().has('name', 'gremlin')).limit(10).profile()
==>Traversal Metrics
Step Count Traversers Time (ms) % Dur
=============================================================================================================
JanusGraphStep([],[~label.eq(develops), since.g... 4550 4550 4692.458 93.10
\_condition=(~label = develops AND since > 2005)
\_isFitted=true
\_query=[(since > 2005)]:by-since-mixed
\_index=by-since-mixed
\_orders=[]
\_isOrdered=true
\_index_impl=search
optimization 0.038
optimization 4.953
backend-query 0.000
\_query=by-since-mixed:[(since > 2005)]:by-since-mixed
backend-query 4550 853.646
\_query=by-since-mixed:[(since > 2005)]:by-since-mixed
AndStep([[EdgeVertexStep(IN), ProfileStep, HasS... 4548 4548 218.376 4.33
EdgeVertexStep(IN) 4550 4550 20.861
HasStep([name.eq(gremlin)]) 148.202
OrderGlobalStep([[value(since), incr]]) 11 11 129.116 2.56
RangeGlobalStep(0,10) 10 10 0.171 0.00
>TOTAL - - 5040.122 -
gremlin> g.tx().rollback()
==>null
gremlin> g.E().hasLabel('develops').has('since', gt(2005)).order().by('since', incr).limit(Integer.MAX_VALUE).and(inV().has('name', 'gremlin')).limit(10).profile()
==>Traversal Metrics
Step Count Traversers Time (ms) % Dur
=============================================================================================================
JanusGraphStep([],[~label.eq(develops), since.g... 11 11 58.687 96.56
\_condition=(~label = develops AND since > 2005)
\_isFitted=true
\_query=[(since > 2005)][ASC(since)]:by-since-mixed
\_index=by-since-mixed
\_orders=[ASC(since)]
\_isOrdered=true
\_index_impl=search
optimization 0.022
optimization 0.861
backend-query 0.000
\_query=by-since-mixed:[(since > 2005)][ASC(since)]:by-since-mixed
AndStep([[EdgeVertexStep(IN), ProfileStep, HasS... 11 11 1.773 2.92
EdgeVertexStep(IN) 11 11 0.057
HasStep([name.eq(gremlin)]) 1.483
RangeGlobalStep(0,10) 10 10 0.315 0.52
>TOTAL - - 60.776 -```
Contributor guide
Research direction
Start with the supplied JanusGraph and Elasticsearch reproduction, comparing the two profiled traversals and the JanusGraphStep metadata for _orders. Done means the ordered query after and(...) pushes ASC(since) into the mixed-index request without requiring limit(Integer.MAX_VALUE), with regression coverage for both traversal forms.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- elasticsearch, java
- Domain
- databases, performance
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 38/100