Geo spatial query takes time on huge EDGE set

Ajay Shenoy

unread,

Sep 2, 2015, 6:03:06 AM9/2/15

to OrientDB

I have created a database with two vertices in it. Stores, Products. Stores have a GeoSpatial Index. There is an Edge 'hasInventory' from store to products which holds quantity for each Product-Store relation. Now If I have to find a particular Product(sku) in near by stores how should I proceed.

I tried like:

Select @rid,name,address,lat,lon,$distance from Stores where @rid IN (Select out.@rid from hasInventory where in.sku = '8907103076739' and quantity > 0) and [lat,lon,$spatial] NEAR [19.195524,72.834810,{'maxDistance' : 20}]

Its gives me result but takes around 14 seconds to give response.

Please suggest a solution for faster response time.

Thanks in advance.

Enrico Risa

unread,

Sep 2, 2015, 6:07:41 AM9/2/15

to orient-...@googlegroups.com

Hi Ajay

can you post the explain of this query?

--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-databa...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Ajay Shenoy

unread,

Sep 2, 2015, 9:26:31 AM9/2/15

to OrientDB

Hi Enrico,

Here's the EXPLAIN response :

{

"result": [

{

"@type": "d",

"@version": 0,

"limit": -1,

"spatial": {

"maxDistance": 20

},

"luceneIndex": true,

"fullySortedByIndex": false,

"evaluated": 432430,

"documentAnalyzedCompatibleClass": 432430,

"Stores_idx_spatial_totalHits": 68,

"distance": 19.755164509212644,

"involvedIndexes": [

"Stores.idx_spatial"

],

"totalHits": 68,

"indexIsUsedInOrderBy": false,

"current": "#12:569",

"fetchingFromTargetElapsed": 14210,

"tips": [

"Query 'SELECT out.@rid FROM hasInventory WHERE in.sku = '8907103076739' AND quantity > 0' fetched more than 50000 records: to speed up the execution, create an index or change the query to use an existent index"

],

"documentReads": 432430,

"recordReads": 432430,

"elapsed": 14213.048,

"resultType": "collection",

"resultSize": 15,

"@fieldTypes": "evaluated=l,documentAnalyzedCompatibleClass=l,distance=d,involvedIndexes=e,current=x,fetchingFromTargetElapsed=l,documentReads=l,recordReads=l,elapsed=f"

}

],

"warnings": [

"Query 'SELECT out.@rid FROM hasInventory WHERE in.sku = '8907103076739' AND quantity > 0' fetched more than 50000 records: to speed up the execution, create an index or change the query to use an existent index"

],

"notification": "Query executed in 14.232 sec. Returned 1 record(s)"

Enrico Risa

unread,

Sep 2, 2015, 9:29:52 AM9/2/15

to orient-...@googlegroups.com

Hi Ajay

if you se the tips

"Query 'SELECT out.@rid FROM hasInventory WHERE in.sku = '8907103076739' AND quantity > 0' fetched more than 50000 records: to speed up the execution, create an index or change the query to use an existent index"

this query is executing full scan on the class has_Inventory is not using any index.

This is slow

Ajay Shenoy

unread,

Sep 2, 2015, 10:22:30 AM9/2/15

to OrientDB

Hi Enrico,

Have introduced two new properies of type link namely 'out' and 'in' on 'hasInventory' Edge and indexed the two properties.

Also tweeked the query as mentioned below :

Select @rid,quantity from hasInventory where in = #16:4 and quantity > 0 and out IN (Select @rid from Stores where [lat,lon,$spatial] NEAR [19.195524,72.834810,{'maxDistance' : 20}] SKIP 0 LIMIT 20)