Select vertices which has incoming vertices from one or two different classes (Optimization)

82 views
Skip to first unread message

bharath dandala

unread,
Sep 28, 2015, 11:28:57 AM9/28/15
to OrientDB
Hi All,
I  have three different classes and  CUI--->SENTENCE <--RELATION

CUI and SENTENCE are connected by hasCui edge.

SENTENCE and RELATION are connect by twrex edge.

I want to find vertices of class B 1) given 2 entries of class CUI   2) One entry from class CUI and one entry from Class RELATION.

I have indexes on ref_id of class A ref_id of class B and ref_id of class C. I also have indexes on hasCui edge.

Here are my queries:


  orientdb {db=emerald}> explain select ref_id from (select expand(in('hasCui')) from CUI where ref_id='C0024530') where out('hasCui').ref_id contains 'C0013090'

rofiled command '{expandElapsed:1,limit:-1,evaluated:2709,current:#58:2852164,fetchingFromTargetElapsed:5124,documentReads:2709,user:#5:0,recordReads:2709,elapsed:5246.187,resultType:collection,resultSize:35}' in 5.247000 sec(s):
{"@type":"d","@version":0,"expandElapsed":1,"limit":-1,"evaluated":2709,"current":"#58:2852164","fetchingFromTargetElapsed":5124,"documentReads":2709,"user":"#5:0","recordReads":2709,"elapsed":5246.187,"resultType":"collection","resultSize":35,"@fieldTypes":"expandElapsed=l,evaluated=l,current=x,fetchingFromTargetElapsed=l,documentReads=l,user=x,recordReads=l,elapsed=f"}




  orientdb {db=emerald}> explain select ref_id from (select expand(in('hasCui')) from CUI where ref_id='C0024530') where out('hasCui') contains 'C0013090' and out('twrex') contains 'treats'

Profiled command '{expandElapsed:0,limit:-1,evaluated:2709,current:#58:2852164,fetchingFromTargetElapsed:4880,documentReads:2709,user:#5:0,recordReads:2709,elapsed:4982.7866,resultType:collection,resultSize:0}' in 4.983000 sec(s):
{"@type":"d","@version":0,"expandElapsed":0,"limit":-1,"evaluated":2709,"current":"#58:2852164","fetchingFromTargetElapsed":4880,"documentReads":2709,"user":"#5:0","recordReads":2709,"elapsed":4982.7866,"resultType":"collection","resultSize":0,"@fieldTypes":"expandElapsed=l,evaluated=l,current=x,fetchingFromTargetElapsed=l,documentReads=l,user=x,recordReads=l,elapsed=f"}

Each query is taking approximately 5 seconds. It seems my queries are not using indexes. Also, I cannot afford to use lightweight edges as my edges has property. I have indexes on my edge hasCui_idx


Here are additional details of my database.

orientdb {db=emerald}> select count(*) from CUI

----+------+-------
#   |@CLASS|count 
----+------+-------
0   |null  |2974236
----+------+-------

orientdb {db=emerald}> select count(*) from Sentence

----+------+-------
#   |@CLASS|count 
----+------+-------
0   |null  |2853697
----+------+-------


orientdb {db=emerald}> select count(*) from RELATION

----+------+-----
#   |@CLASS|count
----+------+-----
0   |null  |12  
----+------+-----

orientdb {db=emerald}> select count(*) from twrex  

----+------+------
#   |@CLASS|count
----+------+------
0   |null  |466611
----+------+------

orientdb {db=emerald}> select count(*) from hasCui

----+------+--------
#   |@CLASS|count  
----+------+--------
0   |null  |49970218
----+------+--------

orientdb {db=emerald}> list indexes                                                                                          


INDEXES
----------------------------------------------+------------+-----------------------+----------------+------------+
 NAME                                         | TYPE       |         CLASS         |     FIELDS     | RECORDS    |
----------------------------------------------+------------+-----------------------+----------------+------------+
 AUI.ref_id                                   | UNIQUE_... | AUI                   | ref_id         |    8736353 |
 AUI_0.ref_id                                 | UNIQUE_... | AUI_0                 | ref_id         |    1747126 |
 AUI_1.ref_id                                 | UNIQUE_... | AUI_1                 | ref_id         |    1747277 |
 AUI_2.ref_id                                 | UNIQUE_... | AUI_2                 | ref_id         |    1747741 |
 AUI_3.ref_id                                 | UNIQUE_... | AUI_3                 | ref_id         |    1746487 |
 AUI_4.ref_id                                 | UNIQUE_... | AUI_4                 | ref_id         |    1747722 |
 CUI.ref_id                                   | UNIQUE_... | CUI                   | ref_id         |    2974236 |
 CUI_0.ref_id                                 | UNIQUE_... | CUI_0                 | ref_id         |     594601 |
 CUI_1.ref_id                                 | UNIQUE_... | CUI_1                 | ref_id         |     594779 |
 CUI_2.ref_id                                 | UNIQUE_... | CUI_2                 | ref_id         |     594842 |
 CUI_3.ref_id                                 | UNIQUE_... | CUI_3                 | ref_id         |     594248 |
 CUI_4.ref_id                                 | UNIQUE_... | CUI_4                 | ref_id         |     594394 |
 dictionary                                   | DICTIONARY |                       |                |          0 |
 ENTITY.ref_id                                | UNIQUE_... | ENTITY                | ref_id         |         22 |
 hasCui_IDX                                   | NOTUNIQUE  | hasCui                | in             |   44048388 |
                                              |            |                       | out            |            |
 LUI.ref_id                                   | UNIQUE_... | LUI                   | ref_id         |    6117226 |
 LUI_0.ref_id                                 | UNIQUE_... | LUI_0                 | ref_id         |    1223326 |
 LUI_1.ref_id                                 | UNIQUE_... | LUI_1                 | ref_id         |    1223955 |
 LUI_2.ref_id                                 | UNIQUE_... | LUI_2                 | ref_id         |    1223304 |
 LUI_3.ref_id                                 | UNIQUE_... | LUI_3                 | ref_id         |    1223100 |
 LUI_4.ref_id                                 | UNIQUE_... | LUI_4                 | ref_id         |    1223541 |
 ORole.name                                   | UNIQUE     | ORole                 | name           |          3 |
 OUser.name                                   | UNIQUE     | OUser                 | name           |          3 |
 RELATION.ref_id                              | UNIQUE_... | RELATION              | ref_id         |         12 |
 SEMANTIC_TYPE.ref_id                         | UNIQUE_... | SEMANTIC_TYPE         | ref_id         |        138 |
 SEMANTIC_TYPE.TUI                            | UNIQUE     | SEMANTIC_TYPE         | TUI            |        133 |
 SUI.ref_id                                   | UNIQUE_... | SUI                   | ref_id         |    7143364 |
 SUI_0.ref_id                                 | UNIQUE_... | SUI_0                 | ref_id         |    1429203 |
 SUI_1.ref_id                                 | UNIQUE_... | SUI_1                 | ref_id         |    1428665 |
 SUI_2.ref_id                                 | UNIQUE_... | SUI_2                 | ref_id         |    1428526 |
 SUI_3.ref_id                                 | UNIQUE_... | SUI_3                 | ref_id         |    1428373 |
 SUI_4.ref_id                                 | UNIQUE_... | SUI_4                 | ref_id         |    1428597 |
----------------------------------------------+------------+-----------------------+----------------+------------+
 TOTAL = 32                                                                                             93989685 |
-----------------------------------------------------------------------------------------------------------------+


Appreciate your help,
Thanks,

Regards,
Bharath

alessand...@gmail.com

unread,
Sep 28, 2015, 11:39:50 AM9/28/15
to OrientDB
Hi,

Can you send me your schema?

Regards,
Alessandro

bharath dandala

unread,
Sep 28, 2015, 11:45:20 AM9/28/15
to OrientDB
Hi Alessandro,
Here is the schema.

Thank you.
Regards,
Bharath
----------------------------------------------+------------------------------------+------------+----------------+
 NAME                                         | SUPERCLASS                         | CLUSTERS   | RECORDS        |
----------------------------------------------+------------------------------------+------------+----------------+
 _studio                                      |                                    | 59         |              4 |
 AQ                                           | E                                  | 44         |         606008 |
 AUI                                          | V                                  | 38         |              0 |
 AUI_0                                        | AUI                                | 39         |        1747126 |
 AUI_1                                        | AUI                                | 40         |        1747277 |
 AUI_2                                        | AUI                                | 41         |        1747741 |
 AUI_3                                        | AUI                                | 42         |        1746487 |
 AUI_4                                        | AUI                                | 43         |        1747722 |
 CHD                                          | E                                  | 45         |        3411387 |
 Corpus                                       | V                                  | 56         |              4 |
 CUI                                          | V                                  | 20         |           1372 |
 CUI_0                                        | CUI                                | 21         |         594601 |
 CUI_1                                        | CUI                                | 22         |         594779 |
 CUI_2                                        | CUI                                | 23         |         594842 |
 CUI_3                                        | CUI                                | 24         |         594248 |
 CUI_4                                        | CUI                                | 25         |         594394 |
 Document                                     | V                                  | 57         |           9067 |
 E                                            |                                    | 10         |              0 |
 ENTITY                                       | V                                  | 18         |             22 |
 hasCui                                       | E                                  | 16         |       49970218 |
 isa                                          | E                                  | 13         |              0 |
 LUI                                          | V                                  | 26         |              0 |
 LUI_0                                        | LUI                                | 27         |        1223326 |
 LUI_1                                        | LUI                                | 28         |        1223955 |
 LUI_2                                        | LUI                                | 29         |        1223304 |
 LUI_3                                        | LUI                                | 30         |        1223100 |
 LUI_4                                        | LUI                                | 31         |        1223541 |
 next                                         | E                                  | 12         |              0 |
 OFunction                                    |                                    | 6          |              0 |
 OIdentity                                    |                                    | -          |              0 |
 ORestricted                                  |                                    | -          |              0 |
 ORIDs                                        |                                    | 8          |              0 |
 ORole                                        | OIdentity                          | 4          |              3 |
 OSchedule                                    |                                    | 7          |              0 |
 OTriggered                                   |                                    | -          |              0 |
 OUser                                        | OIdentity                          | 5          |              3 |
 PAR                                          | E                                  | 46         |        3411387 |
 partOf                                       | E                                  | 11         |        2862764 |
 QB                                           | E                                  | 47         |         606008 |
 RB                                           | E                                  | 48         |        1401463 |
 rel                                          | E                                  | 17         |       25362226 |
 RELATION                                     | V                                  | 19         |             12 |
 RL                                           | E                                  | 49         |          62672 |
 RN                                           | E                                  | 50         |        1401463 |
 RO                                           | E                                  | 51         |       10493889 |
 RQ                                           | E                                  | 52         |         621916 |
 semantic                                     | E                                  | 15         |       56358706 |
 SEMANTIC_TYPE                                | V                                  | 55         |            138 |
 Sentence                                     | V                                  | 58         |        2853697 |
 SIB                                          | E                                  | 53         |       10933344 |
 SUI                                          | V                                  | 32         |              0 |
 SUI_0                                        | SUI                                | 33         |        1429203 |
 SUI_1                                        | SUI                                | 34         |        1428665 |
 SUI_2                                        | SUI                                | 35         |        1428526 |
 SUI_3                                        | SUI                                | 36         |        1428373 |
 SUI_4                                        | SUI                                | 37         |        1428597 |
 SY                                           | E                                  | 54         |        3248686 |
 twrex                                        | E                                  | 14         |         466611 |
 V                                            |                                    | 9          |              0 |
----------------------------------------------+------------------------------------+------------+----------------+
 TOTAL = 59                                                                                            199052877 |
----------------------------------------------+------------------------------------+------------+----------------+

bharath dandala

unread,
Sep 28, 2015, 5:49:17 PM9/28/15
to OrientDB
Hi Alessandro,
Would you please provide some feedback.

Thanks,
Regards,
Bharath

alessand...@gmail.com

unread,
Sep 29, 2015, 3:59:16 AM9/29/15
to OrientDB
Hi,
try this query and let me know if it's faster
select ref_id from sentence let $a = in('hasCui') where $a.ref_id contains 'C0024530' and $a.ref_id contains 'C0013090'

If the relationship between the classes is the following CUI--->SENTENCE <--RELATION
in your first query you should have "select expand(out('hasCui')) from CUI" and not "select expand(in('hasCui')) from CUI"

Alessandro

alessand...@gmail.com

unread,
Sep 29, 2015, 4:12:11 AM9/29/15
to OrientDB
Hi,
also you can try this query "select ref_id from sentence where ['C0024530','C0013090'] in in('hasCui').ref_id"

Alessandro

bharath dandala

unread,
Sep 29, 2015, 11:15:01 AM9/29/15
to OrientDB
Hi Alessandro,
Thank you.

Sorry, I had a small mistake in my post. The query is actually correct. But I posted the relationship wrong. It looks like this CUI<---SENTENCE---->RELATION

orientdb {db=emerald_corpus_with_indexes}> select ref_id from sentence where ['C0024530','C0013090'] in out('hasCui').ref_id                     

----+------+------
#   |@CLASS|ref_id
----+------+------
0   |null  |41   
1   |null  |116  
2   |null  |137  
3   |null  |138  
4   |null  |139  
5   |null  |51   
6   |null  |113  
7   |null  |114  
8   |null  |151  
9   |null  |30   
10  |null  |133  
11  |null  |146  
12  |null  |151  
13  |null  |157  
14  |null  |171  
15  |null  |118  
16  |null  |127  
17  |null  |55   
18  |null  |33   
19  |null  |128  
----+------+------
LIMIT EXCEEDED: resultset contains more items not displayed (limit=20)
20 item(s) found. Query executed in 39.347 sec(s).

orientdb {db=emerald_corpus_with_indexes}> select ref_id from sentence let $a = out('hasCui') where $a.ref_id contains 'C0024530' and $a.ref_id contains 'C0013090'

This query is running forever and it doesn't quit. So I tried this.

orientdb {db=emerald_corpus_with_indexes}> select ref_id from sentence let $a = out('hasCui') where $a.ref_id containstext 'C0024530'

----+------+------
#   |@CLASS|ref_id
----+------+------
0   |null  |113  
1   |null  |114  
2   |null  |55   
3   |null  |33   
4   |null  |54   
5   |null  |85   
6   |null  |106  
7   |null  |114  
8   |null  |50   
9   |null  |82   
10  |null  |83   
11  |null  |113  
12  |null  |2    
13  |null  |3    
14  |null  |29   
15  |null  |31   
16  |null  |35   
17  |null  |42   
18  |null  |44   
19  |null  |46   
----+------+------
LIMIT EXCEEDED: resultset contains more items not displayed (limit=20)
20 item(s) found. Query executed in 113.49 sec(s).

Thanks,

Regards,
Bharath

bharath dandala

unread,
Oct 1, 2015, 11:01:45 AM10/1/15
to OrientDB
HI Alessandro,
Did you get a chance to look into it?

Thanks,

Regards,
Bharath

alessand...@gmail.com

unread,
Oct 1, 2015, 11:19:41 AM10/1/15
to OrientDB
Hi,
can you send me privately the database so that I can test different queries on it ?

Alessandro

bharath dandala

unread,
Oct 1, 2015, 11:49:22 AM10/1/15
to OrientDB
Hi Alessandro,
You can download it here.
https://www.dropbox.com/s/10d2yvt6g2yp9cg/OrientDB_Sample_graph.export.gz?dl=0


Thank you,

Regards,
Bharath

alessand...@gmail.com

unread,
Oct 2, 2015, 9:51:46 AM10/2/15
to OrientDB
Hi Bharath,
I could not test it on the database at the time, however try this query 
select ref_id from (select ref_id, count(@rid) as counts from (select expand(in("hasCui")) from Cui where ref_id='C0024530' or ref_id='C0013090') group by @rid) where counts>1
and let me know.

Kind regards,
Alessandro
Reply all
Reply to author
Forward
0 new messages