Customizing Virtuoso SPARQL Query Optimization using Pragmas and Inline Query Options

What

Virtuoso has a number of pragmas that can be passed in SPARQL queries to control/influence query execution.

Why

Query optimization is an imperfect science that doesn’t always produce optimal outcomes for query solution production pipelines. In situations where this arises, Virtuoso offers the sql:table-option "index {option-id}" pragma as a mechanism for informing a Virtuoso runtime session about query optimization preferences via explicit index selection.

How

Global Usage

Here’s a breakdown on the option identifiers associated with the sql:table-option "index {option-id}" pragma in relation SPARQL query execution:

  • sql:table-option "index G" - Invokes the GS index (or can use GS)
  • sql:table-option "index S" - Invokes the SP index (or can use SP)
  • sql:table-option "index P" - Invokes the POGS index (or can use POGS)
  • sql:table-option "index O" - Invokes the OP index (or can use OP)
  • sql:table-option "index RDF_QUAD" - Invokes the PSOG primary key index

Usage Example

DEFINE sql:table-option "index S"
PREFIX ex: <http://example.org/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>

SELECT ?person ?name 
WHERE {
        VALUES ?person { 
                         <urn:person_0>
                         <urn:person_1>
                        }
         ?person ex:hasSkill ?skill  .
         ?skill rdfs:label ?name .
        } 

Specific Query Usage

If global scope isn’t desired, e.g., in situations where usage is to be scoped to a specific SPARQL query, you simply move option usage to the triple-pattern level as per the examples that follow:

  • option (table_option "hash/loop")
  • option (table_option "index S/O/G/RDF_QUAD/RDF_QUAD_POGS")

Usage Example

PREFIX ex: <http://example.org/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>

SELECT ?person ?name 
WHERE {
        VALUES ?person { 
                         <urn:person_0>
                         <urn:person_1>
                        }
         ?person ex:hasSkill ?skill OPTION (table_option "hash, index RDF_QUAD_POGS") .
         ?skill rdfs:label ?name .
        } 

Note, if an invalid index option is presented the query fails with error SQ188: TABLE OPTION index {option-id} not defined for table DB.DBA.RDF_QUAD .

Related