"optional" slow query

Support
1

i've ~325k triple count

fast

    SELECT
      ?definition
      ?broader
      ?narrower
    WHERE {
      :eca0a3b72707f78df864e19481e8ec92 a skos:Concept .
      OPTIONAL {
        :eca0a3b72707f78df864e19481e8ec92 skos:definition ?definition .
      }
      OPTIONAL {
        :eca0a3b72707f78df864e19481e8ec92 skos:broader ?broaderUri .
        # enable this line will be slow ?broaderUri skosxl:prefLabel ?broaderLabel .
        # ?broaderLabel skosxl:literalForm ?broader .
      }
    #   OPTIONAL {
    #     :eca0a3b72707f78df864e19481e8ec92 skos:narrower ?narrowerUri .
    #     ?narrowerUri skos:prefLabel ?narrower
    #   }
    }

slow

the problem is when i add "?broaderUri skosxl:prefLabel ?broaderLabel ." into query
its very slow

my use case is not all "subject" has such "predict"(skos:broader or skos:narrower etc)

    SELECT
      ?definition
      ?broader
      ?narrower
    WHERE {
      :eca0a3b72707f78df864e19481e8ec92 a skos:Concept .
      OPTIONAL {
        :eca0a3b72707f78df864e19481e8ec92 skos:definition ?definition .
      }
      OPTIONAL {
        :eca0a3b72707f78df864e19481e8ec92 skos:broader ?broaderUri .
        ?broaderUri skosxl:prefLabel ?broaderLabel .
        # ?broaderLabel skosxl:literalForm ?broader .
      }
    #   OPTIONAL {
    #     :eca0a3b72707f78df864e19481e8ec92 skos:narrower ?narrowerUri .
    #     ?narrowerUri skos:prefLabel ?narrower
    #   }
    }

here is query plan

Distinct [#34]
`─ Projection(?definition, ?broader, ?narrower, ?broaderUri) [#34]
   `─ NestedLoopJoinOuter(_) [#34]
      +─ NestedLoopJoinOuter(_) [#34]
      │  +─ Union [#34]
      │  │  +─ Union [#5]
      │  │  │  +─ Scan[SPOC](:eca0a3b72707f78df864e19481e8ec92, rdf:type, <http://www.w3.org/2004/02/skos/core#Concept>) [#1]
      │  │  │  `─ PropertyPath(:eca0a3b72707f78df864e19481e8ec92 -> ?bjbccztv, minLength=1) [#4]
      │  │  │     `─ Union [#2]
      │  │  │        +─ Scan[POSC](?bjbccztv, <http://www.w3.org/2004/02/skos/core#narrower>, :eca0a3b72707f78df864e19481e8ec92) [#1]
      │  │  │        `─ Scan[POSC](?bjbccztv, <http://www.w3.org/2004/02/skos/core#narrower>, :eca0a3b72707f78df864e19481e8ec92) [#1]
      │  │  `─ Union [#29]
      │  │     +─ PropertyPath(?jayatpds -> :eca0a3b72707f78df864e19481e8ec92, minLength=1, reversed) [#12]
      │  │     │  `─ Union [#6]
      │  │     │     +─ Scan[SPOC](:eca0a3b72707f78df864e19481e8ec92, <http://www.w3.org/2004/02/skos/core#narrower>, ?jayatpds) [#3]
      │  │     │     `─ Scan[SPOC](:eca0a3b72707f78df864e19481e8ec92, <http://www.w3.org/2004/02/skos/core#narrower>, ?jayatpds) [#3]
      │  │     `─ Union [#16]
      │  │        +─ PropertyPath(:eca0a3b72707f78df864e19481e8ec92 -> ?nqjentph, minLength=1) [#12]
      │  │        │  `─ Union [#6]
      │  │        │     +─ Scan[SPOC](:eca0a3b72707f78df864e19481e8ec92, <http://www.w3.org/2004/02/skos/core#narrower>, ?nqjentph) [#3]
      │  │        │     `─ Scan[SPOC](:eca0a3b72707f78df864e19481e8ec92, <http://www.w3.org/2004/02/skos/core#narrower>, ?nqjentph) [#3]
      │  │        `─ PropertyPath(?tsdwflim -> :eca0a3b72707f78df864e19481e8ec92, minLength=1, reversed) [#4]
      │  │           `─ Union [#2]
      │  │              +─ Scan[POSC](?tsdwflim, <http://www.w3.org/2004/02/skos/core#narrower>, :eca0a3b72707f78df864e19481e8ec92) [#1]
      │  │              `─ Scan[POSC](?tsdwflim, <http://www.w3.org/2004/02/skos/core#narrower>, :eca0a3b72707f78df864e19481e8ec92) [#1]
      │  `─ Scan[SPOC](:eca0a3b72707f78df864e19481e8ec92, <http://www.w3.org/2004/02/skos/core#definition>, ?definition) [#1]
      `─ MergeJoin(?broaderUri) [#1]
         +─ Scan[PS_NOC](?broaderUri, <http://www.w3.org/2008/05/skos-xl#prefLabel>, _) [#44K]
         `─ Scan[POSC](?broaderUri, <http://www.w3.org/2004/02/skos/core#narrower>, :eca0a3b72707f78df864e19481e8ec92) [#1]

Backup Storage Directory : .backup
CPU Load                 : 0.64 %
Connection Timeout       : 10m
Export Storage Directory : .exports
Memory Heap              : 874M (Max: 1.9G)
Memory Mode              : DEFAULT{Starrocks.block_cache=20, Starrocks.dict_block_cache=10, Native.starrocks=70, Heap.dict_value=50, Starrocks.txn_block_cache=5, Heap.dict_index=50, Starrocks.memtable=40, Starrocks.untracked_memory=20, Starrocks.buffer_pool=5, Native.query=30}
Memory Query Blocks      :   0B (Max: 373M)
Memory RSS               : 1.2G
Named Graph Security     : false
Platform Arch            : x86_64
Platform OS              : Mac OS X 10.15.7, Java 1.8.0_251
Query All Graphs         : false
Query Timeout            : 5m
Security Disabled        : false
Stardog Home             : /Users/monsterstep/dev/stardog_home
Stardog Version          : 7.4.5
Strict Parsing           : true
Uptime                   : 4 days 5 hours 4 minutes 56 seconds
Databases                :
+----------+-------------+------------------------------------------------------+--------------------------------------------+
|          |             |                     Transactions                     |                  Queries                   |
+----------+-------------+-------+-------+-----------+---------------+----------+---------+-------+---------------+----------+
| Database | Connections | Open  | Total | Avg. Size | Avg. Time (s) | Rate/sec | Running | Total | Avg. Time (s) | Rate/sec |
+----------+-------------+-------+-------+-----------+---------------+----------+---------+-------+---------------+----------+
| app      |           0 |     0 |     6 |      565K |         8.445 |    0.000 |       0 |   661 |        26.001 |    0.002 |
| demo     |           0 |     0 |     0 |         0 |         0.000 |    0.000 |       0 |    20 |         0.002 |    0.000 |
| lawtime  |           0 |     0 |     0 |         0 |         0.000 |    0.000 |       0 |     8 |         0.000 |    0.000 |
| test     |           0 |     0 |     0 |         0 |         0.000 |    0.000 |       0 |     8 |         0.000 |    0.000 |
+----------+-------------+-------+-------+-----------+---------------+----------+---------+-------+---------------+----------+
2

Hi crapthings,

Can you run stardog-admin db optimize and see if the performance and/or plan changes?

1 Like
3

@jess it looks good for this query
in stardog studio 200ms ~ 300ms after optimize

should i always run this command after

stardog data add app x.ttl
stardog-admin db optimize app
4

No, that shouldn't be necessary after every update. We normally suggest it after bulk updates which modify a large amount of triples (esp. if large numbers of triples are deleted).

It is unusual to see a very large performance drop on such a small scale (but again, performance is more a function of the total number of triples present, added, and deleted since the last optimize). If you could describe your updates, that could help us reproduce the issue.

Also when one query plan is much faster than the other, we need to see both of them.

Best,
Pavel

5

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.