Index not being used with 'Between" clause


Gene Fojtik <genef...@...>
 

Hello,

Have an index in a property "latitude", when using with the between clause, the index is not being utilized

g.V().has("latitude", 33.333")  works well, however

g.V().has(“latitude”, between(33.889, 33.954)))  does not use the indexes..

Any assistance would be appreciated..

-g


Jason Plurad <plu...@...>
 

Make sure you're using a mixed index for numeric range queries. Composite indexes are best for exact matching. The console session below shows the difference:

gremlin> graph = JanusGraphFactory.open('conf/janusgraph-berkeleyje-lucene.properties')
==>standardjanusgraph[berkeleyje:/usr/lib/janusgraph-0.1.1-hadoop2/conf/../db/berkeley]
gremlin
> mgmt = graph.openManagement()
==>org.janusgraph.graphdb.database.management.ManagementSystem@1c8f6a90
gremlin
> lat = mgmt.makePropertyKey('lat').dataType(Integer.class).make()
==>lat
gremlin
> latidx = mgmt.buildIndex('latidx', Vertex.class).addKey(lat).buildCompositeIndex()
==>latidx
gremlin
> lon = mgmt.makePropertyKey('lon').dataType(Integer.class).make()
==>lon
gremlin
> lonidx = mgmt.buildIndex('lonidx', Vertex.class).addKey(lon).buildMixedIndex('search')
==>lonidx
gremlin
> mgmt.commit()
==>null
gremlin
> v = graph.addVertex('code', 'rdu', 'lat', 35, 'lon', -78)
==>v[4184]
gremlin
> g = graph.traversal()
==>graphtraversalsource[standardjanusgraph[berkeleyje:/usr/lib/janusgraph-0.1.1-hadoop2/conf/../db/berkeley], standard]
gremlin
> g.V().has('lat', 35)
==>v[4184]
gremlin
> g.V().has('lat', between(34, 36))
00:40:33 WARN  org.janusgraph.graphdb.transaction.StandardJanusGraphTx  - Query requires iterating over all vertices [(lat >= 34 AND lat < 36)]. For better performance, use indexes
==>v[4184]
gremlin
> g.V().has('lon', -78)
==>v[4184]
gremlin
> g.V().has('lon', between(-79, -77))
==>v[4184]

-- Jason


On Wednesday, June 7, 2017 at 12:01:31 PM UTC-4, Gene Fojtik wrote:
Hello,

Have an index in a property "latitude", when using with the between clause, the index is not being utilized

g.V().has("latitude", 33.333")  works well, however

g.V().has(“latitude”, between(33.889, 33.954)))  does not use the indexes..

Any assistance would be appreciated..

-g


Gene Fojtik <genef...@...>
 

Outstanding - thank you Jason.

-gene


On Thursday, June 8, 2017 at 11:47:53 PM UTC-5, Jason Plurad wrote:
Make sure you're using a mixed index for numeric range queries. Composite indexes are best for exact matching. The console session below shows the difference:

gremlin> graph = JanusGraphFactory.open('conf/janusgraph-berkeleyje-lucene.properties')
==>standardjanusgraph[berkeleyje:/usr/lib/janusgraph-0.1.1-hadoop2/conf/../db/berkeley]
gremlin
> mgmt = graph.openManagement()
==>org.janusgraph.graphdb.database.management.ManagementSystem@1c8f6a90
gremlin
> lat = mgmt.makePropertyKey('lat').dataType(Integer.class).make()
==>lat
gremlin
> latidx = mgmt.buildIndex('latidx', Vertex.class).addKey(lat).buildCompositeIndex()
==>latidx
gremlin
> lon = mgmt.makePropertyKey('lon').dataType(Integer.class).make()
==>lon
gremlin
> lonidx = mgmt.buildIndex('lonidx', Vertex.class).addKey(lon).buildMixedIndex('search')
==>lonidx
gremlin
> mgmt.commit()
==>null
gremlin
> v = graph.addVertex('code', 'rdu', 'lat', 35, 'lon', -78)
==>v[4184]
gremlin
> g = graph.traversal()
==>graphtraversalsource[standardjanusgraph[berkeleyje:/usr/lib/janusgraph-0.1.1-hadoop2/conf/../db/berkeley], standard]
gremlin
> g.V().has('lat', 35)
==>v[4184]
gremlin
> g.V().has('lat', between(34, 36))
00:40:33 WARN  org.janusgraph.graphdb.transaction.StandardJanusGraphTx  - Query requires iterating over all vertices [(lat >= 34 AND lat < 36)]. For better performance, use indexes
==>v[4184]
gremlin
> g.V().has('lon', -78)
==>v[4184]
gremlin
> g.V().has('lon', between(-79, -77))
==>v[4184]

-- Jason

On Wednesday, June 7, 2017 at 12:01:31 PM UTC-4, Gene Fojtik wrote:
Hello,

Have an index in a property "latitude", when using with the between clause, the index is not being utilized

g.V().has("latitude", 33.333")  works well, however

g.V().has(“latitude”, between(33.889, 33.954)))  does not use the indexes..

Any assistance would be appreciated..

-g