janusgraph-users@lists.lfaidata.foundation | Very Slow query with Composite and Mixed Index [Janusgraph + CQL + ES]

"mr.morpheus2020" <mr.morp...@...>

I have a test case where I have inserted 50K V() with 100 different labels. Setup uses https://github.com/JanusGraph/janusgraph-docker/blob/master/docker-compose-cql-es.yml as is

when I have a few 100K vertices, the query times out. I guess for the ES query the Count becomes too large

1> What would be the best design for the data

2> Should i create Label based Index?

3> Is there are different query I should try.

In my case in future i might have 100k+ labels

>> Executing query 1 [g.V().has('label_ref','label-1').has('create_ts', P.gt(0)).has('update_ts', P.lte(System.currentTimeMillis())).profile()]

Traversal Metrics
Step                                                               Count  Traversers       Time (ms)    % Dur
=============================================================================================================
JanusGraphStep([],[label_ref.eq(label-1), creat...                   500         500        5009.712   100.00
    \_condition=(label_ref = label-1 AND create_ts > 0 AND update_ts <= 1593097232645)
    \_isFitted=true
    \_query=[(update_ts <= 1593097232645)]:updateTsIndex
    \_index=updateTsIndex
    \_orders=[]
    \_isOrdered=true
    \_index_impl=search
  optimization                                                                                 0.021
  optimization                                                                                 1.160
  backend-query                                                    49999                    2653.017
    \_query=createTs:[(create_ts > 0)]:createTs
  backend-query                                                    50000                    2261.454
    \_query=updateTsIndex:[(update_ts <= 1593097232645)]:updateTsIndex
  backend-query                                                      500                      91.058
    \_query=labelRefIndex:multiKSQ[1]@2147483647
                                            >TOTAL                     -           -        5009.712        -
Script execution complete, server configuration complete.

Idx Creation

//COMPOSITE INDEX
labelRef = mgmt.makePropertyKey('label_ref').dataType(String.class).make()
mgmt.buildIndex('labelRefIndex', Vertex.class).addKey(labelRef).buildCompositeIndex()
//MIXED INDEX
createTs = mgmt.makePropertyKey('create_ts').dataType(Long.class).make()
mgmt.buildIndex('createTs', Vertex.class).addKey(createTs).buildMixedIndex('search')
//MIXED INDEX
updateTs = mgmt.makePropertyKey('update_ts').dataType(Long.class).make()
mgmt.buildIndex('updateTsIndex', Vertex.class).addKey(updateTs).buildMixedIndex('search')

I used "/docker-entrypoint-initdb.d" at startup to create the indices and init data. The complete snippet is here https://gitlab.com/snippets/1990137

Any help is appreciated!

sparshneel chanchlani <sparshneel...@...>

#4938

What i have osbserved the mixed index is really fast with String fields. Also when u use String filed for the mixed index would suggest use textContains coz mixed index do not perform well for eq operation

Show quoted text

On 25-Jun-2020, at 21:17, mr.morpheus2020 <mr.morp...@...> wrote:
I have a test case where I have inserted 50K V() with 100 different labels. Setup uses https://github.com/JanusGraph/janusgraph-docker/blob/master/docker-compose-cql-es.yml as is

when I have a few 100K vertices, the query times out. I guess for the ES query the Count becomes too large

1> What would be the best design for the data
2> Should i create Label based Index?
3> Is there are different query I should try.

In my case in future i might have 100k+ labels
>> Executing query 1 [g.V().has('label_ref','label-1').has('create_ts', P.gt(0)).has('update_ts', P.lte(System.currentTimeMillis())).profile()] Traversal Metrics Step Count Traversers Time (ms) % Dur ============================================================================================================= JanusGraphStep([],[label_ref.eq(label-1), creat... 500 500 5009.712 100.00 \_condition=(label_ref = label-1 AND create_ts > 0 AND update_ts <= 1593097232645) \_isFitted=true \_query=[(update_ts <= 1593097232645)]:updateTsIndex \_index=updateTsIndex \_orders=[] \_isOrdered=true \_index_impl=search optimization 0.021 optimization 1.160 backend-query 49999 2653.017 \_query=createTs:[(create_ts > 0)]:createTs backend-query 50000 2261.454 \_query=updateTsIndex:[(update_ts <= 1593097232645)]:updateTsIndex backend-query 500 91.058 \_query=labelRefIndex:multiKSQ[1]@2147483647 >TOTAL - - 5009.712 - Script execution complete, server configuration complete.

Idx Creation
//COMPOSITE INDEX
labelRef = mgmt.makePropertyKey('label_ref').dataType(String.class).make()
mgmt.buildIndex('labelRefIndex', Vertex.class).addKey(labelRef).buildCompositeIndex()
//MIXED INDEX
createTs = mgmt.makePropertyKey('create_ts').dataType(Long.class).make()
mgmt.buildIndex('createTs', Vertex.class).addKey(createTs).buildMixedIndex('search')
//MIXED INDEX
updateTs = mgmt.makePropertyKey('update_ts').dataType(Long.class).make()
mgmt.buildIndex('updateTsIndex', Vertex.class).addKey(updateTs).buildMixedIndex('search')
I used "/docker-entrypoint-initdb.d" at startup to create the indices and init data. The complete snippet is here https://gitlab.com/snippets/1990137

Any help is appreciated!
--
You received this message because you are subscribed to the Google Groups "JanusGraph users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to janusgra...@....
To view this discussion on the web visit https://groups.google.com/d/msgid/janusgraph-users/0a5c0d5c-14ca-4f10-bd4d-6e64018f2b74o%40googlegroups.com.

BO XUAN LI <libo...@...>

#4939

Note that doing an index search with multiple indices being used is often very time-consuming.

If there are not many vertices for a particular ‘label’ (in this example, there are only 500 vertices), I would suggest do an index query against ‘label’ field only, and do an in-memory filtering in your application logic.

Show quoted text

On Jun 26, 2020, at 12:12 AM, sparshneel chanchlani <sparshneel...@...> wrote:
What i have osbserved the mixed index is really fast with String fields. Also when u use String filed for the mixed index would suggest use textContains coz mixed index do not perform well for eq operation
On 25-Jun-2020, at 21:17, mr.morpheus2020 <mr.morp...@...> wrote:
I have a test case where I have inserted 50K V() with 100 different labels. Setup uses https://github.com/JanusGraph/janusgraph-docker/blob/master/docker-compose-cql-es.yml as is

when I have a few 100K vertices, the query times out. I guess for the ES query the Count becomes too large

1> What would be the best design for the data
2> Should i create Label based Index?
3> Is there are different query I should try.

In my case in future i might have 100k+ labels
>> Executing query 1 [g.V().has('label_ref','label-1').has('create_ts', P.gt(0)).has('update_ts', P.lte(System.currentTimeMillis())).profile()] Traversal Metrics Step Count Traversers Time (ms) % Dur ============================================================================================================= JanusGraphStep([],[label_ref.eq(label-1), creat... 500 500 5009.712 100.00 \_condition=(label_ref = label-1 AND create_ts > 0 AND update_ts <= 1593097232645) \_isFitted=true \_query=[(update_ts <= 1593097232645)]:updateTsIndex \_index=updateTsIndex \_orders=[] \_isOrdered=true \_index_impl=search optimization 0.021 optimization 1.160 backend-query 49999 2653.017 \_query=createTs:[(create_ts > 0)]:createTs backend-query 50000 2261.454 \_query=updateTsIndex:[(update_ts <= 1593097232645)]:updateTsIndex backend-query 500 91.058 \_query=labelRefIndex:multiKSQ[1]@2147483647 >TOTAL - - 5009.712 - Script execution complete, server configuration complete.

Idx Creation
//COMPOSITE INDEX
labelRef = mgmt.makePropertyKey('label_ref').dataType(String.class).make()
mgmt.buildIndex('labelRefIndex', Vertex.class).addKey(labelRef).buildCompositeIndex()
//MIXED INDEX
createTs = mgmt.makePropertyKey('create_ts').dataType(Long.class).make()
mgmt.buildIndex('createTs', Vertex.class).addKey(createTs).buildMixedIndex('search')
//MIXED INDEX
updateTs = mgmt.makePropertyKey('update_ts').dataType(Long.class).make()
mgmt.buildIndex('updateTsIndex', Vertex.class).addKey(updateTs).buildMixedIndex('search')
I used "/docker-entrypoint-initdb.d" at startup to create the indices and init data. The complete snippet is here https://gitlab.com/snippets/1990137

Any help is appreciated!
--
You received this message because you are subscribed to the Google Groups "JanusGraph users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to janusgra...@....
To view this discussion on the web visit https://groups.google.com/d/msgid/janusgraph-users/0a5c0d5c-14ca-4f10-bd4d-6e64018f2b74o%40googlegroups.com.
--
You received this message because you are subscribed to the Google Groups "JanusGraph users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to janusgra...@....
To view this discussion on the web visit https://groups.google.com/d/msgid/janusgraph-users/7E3C06A0-9DC2-436D-B585-09665B413039%40gmail.com.

"alex...@gmail.com" <alexand...@...>

#4937

Try the next index instead of those 3 indices: mgmt.buildIndex('betterIndexForSuchQuery', Vertex.class).addKey(labelRef).addKey(createTs).addKey(updateTs).buildMixedIndex('search')

Show quoted text

On Thursday, June 25, 2020 at 7:07:01 PM UTC+3 mr.morpheus2020 wrote:

I have a test case where I have inserted 50K V() with 100 different labels. Setup uses https://github.com/JanusGraph/janusgraph-docker/blob/master/docker-compose-cql-es.yml as is

when I have a few 100K vertices, the query times out. I guess for the ES query the Count becomes too large

1> What would be the best design for the data
2> Should i create Label based Index?
3> Is there are different query I should try.

In my case in future i might have 100k+ labels
>> Executing query 1 [g.V().has('label_ref','label-1').has('create_ts', P.gt(0)).has('update_ts', P.lte(System.currentTimeMillis())).profile()] Traversal Metrics Step Count Traversers Time (ms) % Dur ============================================================================================================= JanusGraphStep([],[label_ref.eq(label-1), creat... 500 500 5009.712 100.00 \_condition=(label_ref = label-1 AND create_ts > 0 AND update_ts <= 1593097232645) \_isFitted=true \_query=[(update_ts <= 1593097232645)]:updateTsIndex \_index=updateTsIndex \_orders=[] \_isOrdered=true \_index_impl=search optimization 0.021 optimization 1.160 backend-query 49999 2653.017 \_query=createTs:[(create_ts > 0)]:createTs backend-query 50000 2261.454 \_query=updateTsIndex:[(update_ts <= 1593097232645)]:updateTsIndex backend-query 500 91.058 \_query=labelRefIndex:multiKSQ[1]@2147483647 >TOTAL - - 5009.712 - Script execution complete, server configuration complete.

Idx Creation
//COMPOSITE INDEX
labelRef = mgmt.makePropertyKey('label_ref').dataType(String.class).make()
mgmt.buildIndex('labelRefIndex', Vertex.class).addKey(labelRef).buildCompositeIndex()
//MIXED INDEX
createTs = mgmt.makePropertyKey('create_ts').dataType(Long.class).make()
mgmt.buildIndex('createTs', Vertex.class).addKey(createTs).buildMixedIndex('search')
//MIXED INDEX
updateTs = mgmt.makePropertyKey('update_ts').dataType(Long.class).make()
mgmt.buildIndex('updateTsIndex', Vertex.class).addKey(updateTs).buildMixedIndex('search')
I used "/docker-entrypoint-initdb.d" at startup to create the indices and init data. The complete snippet is here https://gitlab.com/snippets/1990137

Any help is appreciated!