Query still slow after optimize db

crapthings · January 12, 2021, 6:44am

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]

pavel · January 12, 2021, 8:17am

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

crapthings · January 12, 2021, 8:21am

can i send db export privately?

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

pavel · January 12, 2021, 8:25am

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

Best,
Pavel

crapthings · January 12, 2021, 8:25am

thanks @pavel

link messaged

the above query takes 178221ms

pavel · January 12, 2021, 8:57am

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

Best,
Pavel

pavel · January 21, 2021, 11:51am

Again, sorry for the delayed response.

There appears to be an issue in the reasoning engine which manifests in redundant patterns like

            │     │  `─ Union [#177K]
            │     │     +─ Scan[PSOC](?jzylpmui, <http://www.w3.org/2004/02/skos/core#narrower>, ?s) [#89K]
            │     │     `─ Scan[PSOC](?jzylpmui, <http://www.w3.org/2004/02/skos/core#narrower>, ?s) [#89K]

What is worse, they are property path expressions, so they are applied recursively when this query is executed. I have verified that they are the main problem. If changed to just a single narrower scan, this query runs in about 10s.

I created a ticket for this: PLAT-1919. In the meantime, you can inspect your archetypes to see how core:Concept is related to narrower and whether some of that can be simplified.

This has little to do with db optimize.

Best,
Pavel

system · February 4, 2021, 11:51am

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

pavel · February 4, 2021, 2:00pm

A follow-up: a fix for PLAT-1919 was merged and will be included in the March release.

Again, thanks for bringing this to our attention.

Pavel

Topic		Replies	Views
Query Performance Has Changed Support	5	341	October 21, 2019
Query performance issues Support	8	1161	February 28, 2017
Query Throughtput Stardog Support	5	500	May 16, 2017
Scalability issues in Stardog Cloud Support	14	313	March 16, 2024
Interesting performance issue Support	5	250	August 1, 2019

Query still slow after optimize db

Related Topics