Query still slow after optimize db

i think my query is very simple.

the reasoning is open.

i've already optimize db.
but still found slow query.
db is about 9m triples.

stardog 7.4.5
2 core cpu with 8gb ram vps
SELECT *
WHERE {
  ?s a skos:Concept .
  ?s skosxl:prefLabel/skosxl:literalForm ?label .
}
Slice(offset=0, limit=10) [#10]
`─ Distinct [#1.5M]
   `─ Projection(?s, ?label) [#1.5M]
      `─ Union [#1.5M]
         +─ Union [#437K]
         │  +─ MergeJoin(?tlpecumf) [#82K]
         │  │  +─ Scan[PSOC](?tlpecumf, <http://www.w3.org/2008/05/skos-xl#literalForm>, ?label) [#87K]
         │  │  `─ Sort(?tlpecumf) [#82K]
         │  │     `─ MergeJoin(?s) [#82K]
         │  │        +─ Scan[POSC](?s, rdf:type, <http://www.w3.org/2004/02/skos/core#Concept>) [#82K]
         │  │        `─ Scan[PSOC](?s, <http://www.w3.org/2008/05/skos-xl#prefLabel>, ?tlpecumf) [#82K]
         │  `─ MergeJoin(?tlpecumf) [#354K]
         │     +─ Scan[PSOC](?tlpecumf, <http://www.w3.org/2008/05/skos-xl#literalForm>, ?label) [#87K]
         │     `─ Sort(?tlpecumf) [#354K]
         │        `─ MergeJoin(?s) [#354K]
         │           +─ PropertyPath(?s -> ?ohztivgm, minLength=1, sortedBy=?s) [#354K]
         │           │  `─ Union [#177K]
         │           │     +─ Scan[POSC](?ohztivgm, <http://www.w3.org/2004/02/skos/core#narrower>, ?s) [#89K]
         │           │     `─ Scan[POSC](?ohztivgm, <http://www.w3.org/2004/02/skos/core#narrower>, ?s) [#89K]
         │           `─ Scan[PSOC](?s, <http://www.w3.org/2008/05/skos-xl#prefLabel>, ?tlpecumf) [#82K]
         `─ Union [#1.1M]
            +─ MergeJoin(?tlpecumf) [#354K]
            │  +─ Scan[PSOC](?tlpecumf, <http://www.w3.org/2008/05/skos-xl#literalForm>, ?label) [#87K]
            │  `─ Sort(?tlpecumf) [#354K]
            │     `─ MergeJoin(?s) [#354K]
            │        +─ PropertyPath(?zvmgynnx -> ?s, minLength=1, sortedBy=?s, reversed) [#354K]
            │        │  `─ Union [#177K]
            │        │     +─ Scan[PSOC](?s, <http://www.w3.org/2004/02/skos/core#narrower>, ?zvmgynnx) [#89K]
            │        │     `─ Scan[PSOC](?s, <http://www.w3.org/2004/02/skos/core#narrower>, ?zvmgynnx) [#89K]
            │        `─ Scan[PSOC](?s, <http://www.w3.org/2008/05/skos-xl#prefLabel>, ?tlpecumf) [#82K]
            `─ Union [#709K]
               +─ MergeJoin(?tlpecumf) [#354K]
               │  +─ Scan[PSOC](?tlpecumf, <http://www.w3.org/2008/05/skos-xl#literalForm>, ?label) [#87K]
               │  `─ Sort(?tlpecumf) [#354K]
               │     `─ MergeJoin(?s) [#354K]
               │        +─ PropertyPath(?s -> ?ibultvjs, minLength=1, sortedBy=?s) [#354K]
               │        │  `─ Union [#177K]
               │        │     +─ Scan[PSOC](?s, <http://www.w3.org/2004/02/skos/core#narrower>, ?ibultvjs) [#89K]
               │        │     `─ Scan[PSOC](?s, <http://www.w3.org/2004/02/skos/core#narrower>, ?ibultvjs) [#89K]
               │        `─ Scan[PSOC](?s, <http://www.w3.org/2008/05/skos-xl#prefLabel>, ?tlpecumf) [#82K]
               `─ MergeJoin(?tlpecumf) [#354K]
                  +─ Scan[PSOC](?tlpecumf, <http://www.w3.org/2008/05/skos-xl#literalForm>, ?label) [#87K]
                  `─ Sort(?tlpecumf) [#354K]
                     `─ MergeJoin(?s) [#354K]
                        +─ PropertyPath(?aqxxjglk -> ?s, minLength=1, sortedBy=?s, reversed) [#354K]
                        │  `─ Union [#177K]
                        │     +─ Scan[POSC](?aqxxjglk, <http://www.w3.org/2004/02/skos/core#narrower>, ?s) [#89K]
                        │     `─ Scan[POSC](?aqxxjglk, <http://www.w3.org/2004/02/skos/core#narrower>, ?s) [#89K]
                        `─ Scan[PSOC](?s, <http://www.w3.org/2008/05/skos-xl#prefLabel>, ?tlpecumf) [#82K]

this is htop cpu mem chart

it looks when i query it use about 1.3g memory and cpu goes 100

tested on dev server

dev log

Slice(offset=0, limit=10) [#10]
`─ Distinct [#1.6M]
   `─ Projection(?s, ?label) [#1.6M]
      `─ Union [#1.6M]
         +─ Union [#480K]
         │  +─ MergeJoin(?nvrqepnd) [#91K]
         │  │  +─ Scan[PSOC](?nvrqepnd, <http://www.w3.org/2008/05/skos-xl#literalForm>, ?label) [#79K]
         │  │  `─ Sort(?nvrqepnd) [#82K]
         │  │     `─ MergeJoin(?s) [#82K]
         │  │        +─ Scan[POSC](?s, rdf:type, <http://www.w3.org/2004/02/skos/core#Concept>) [#75K]
         │  │        `─ Scan[PSOC](?s, <http://www.w3.org/2008/05/skos-xl#prefLabel>, ?nvrqepnd) [#75K]
         │  `─ MergeJoin(?nvrqepnd) [#389K]
         │     +─ Scan[PSOC](?nvrqepnd, <http://www.w3.org/2008/05/skos-xl#literalForm>, ?label) [#79K]
         │     `─ Sort(?nvrqepnd) [#354K]
         │        `─ MergeJoin(?s) [#354K]
         │           +─ PropertyPath(?s -> ?lcirczgx, minLength=1, sortedBy=?s) [#322K]
         │           │  `─ Union [#161K]
         │           │     +─ Scan[POSC](?lcirczgx, <http://www.w3.org/2004/02/skos/core#narrower>, ?s) [#81K]
         │           │     `─ Scan[POSC](?lcirczgx, <http://www.w3.org/2004/02/skos/core#narrower>, ?s) [#81K]
         │           `─ Scan[PSOC](?s, <http://www.w3.org/2008/05/skos-xl#prefLabel>, ?nvrqepnd) [#75K]
         `─ Union [#1.2M]
            +─ MergeJoin(?nvrqepnd) [#389K]
            │  +─ Scan[PSOC](?nvrqepnd, <http://www.w3.org/2008/05/skos-xl#literalForm>, ?label) [#79K]
            │  `─ Sort(?nvrqepnd) [#354K]
            │     `─ MergeJoin(?s) [#354K]
            │        +─ PropertyPath(?gpoifgtv -> ?s, minLength=1, sortedBy=?s, reversed) [#322K]
            │        │  `─ Union [#161K]
            │        │     +─ Scan[PSOC](?s, <http://www.w3.org/2004/02/skos/core#narrower>, ?gpoifgtv) [#81K]
            │        │     `─ Scan[PSOC](?s, <http://www.w3.org/2004/02/skos/core#narrower>, ?gpoifgtv) [#81K]
            │        `─ Scan[PSOC](?s, <http://www.w3.org/2008/05/skos-xl#prefLabel>, ?nvrqepnd) [#75K]
            `─ Union [#779K]
               +─ MergeJoin(?nvrqepnd) [#389K]
               │  +─ Scan[PSOC](?nvrqepnd, <http://www.w3.org/2008/05/skos-xl#literalForm>, ?label) [#79K]
               │  `─ Sort(?nvrqepnd) [#354K]
               │     `─ MergeJoin(?s) [#354K]
               │        +─ PropertyPath(?s -> ?pykstute, minLength=1, sortedBy=?s) [#322K]
               │        │  `─ Union [#161K]
               │        │     +─ Scan[PSOC](?s, <http://www.w3.org/2004/02/skos/core#narrower>, ?pykstute) [#81K]
               │        │     `─ Scan[PSOC](?s, <http://www.w3.org/2004/02/skos/core#narrower>, ?pykstute) [#81K]
               │        `─ Scan[PSOC](?s, <http://www.w3.org/2008/05/skos-xl#prefLabel>, ?nvrqepnd) [#75K]
               `─ MergeJoin(?nvrqepnd) [#389K]
                  +─ Scan[PSOC](?nvrqepnd, <http://www.w3.org/2008/05/skos-xl#literalForm>, ?label) [#79K]
                  `─ Sort(?nvrqepnd) [#354K]
                     `─ MergeJoin(?s) [#354K]
                        +─ PropertyPath(?arvkrxnf -> ?s, minLength=1, sortedBy=?s, reversed) [#322K]
                        │  `─ Union [#161K]
                        │     +─ Scan[POSC](?arvkrxnf, <http://www.w3.org/2004/02/skos/core#narrower>, ?s) [#81K]
                        │     `─ Scan[POSC](?arvkrxnf, <http://www.w3.org/2004/02/skos/core#narrower>, ?s) [#81K]
                        `─ Scan[PSOC](?s, <http://www.w3.org/2008/05/skos-xl#prefLabel>, ?nvrqepnd) [#75K]

I don't think the issue is with the query plan. It looks more like it's due to complexity in your schema (ontology). Can you share it?

Also how long does the query take?

Cheers,
Pavel

can i send db export privately?

./stardog-admin db create -o database.archetypes="dcterms,skos-inline,skosxl,skosthes,uneskos,skosextends" -n app

Sure. Perhaps you can put it up to some online storage and share the link with me privately, at pavel@stardog.com

Best,
Pavel

thanks @pavel

link messaged

the above query takes 178221ms

Thanks, got it. Will try to take a look in the next couple days.

Best,
Pavel

1 Like