Slow subject-only basic-graph-pattern (i.e. DESCRIBE) using librdf.sqlite (https://github.com/mro/librdf.sqlite)

describe_query.sql

SELECT
 -- all *_id (hashes):
  id
  ,s_uri_id
  ,s_blank_id
  ,p_uri_id
  ,o_uri_id
  ,o_blank_id
  ,o_lit_id
  ,o_datatype_id
  ,c_uri_id
 -- all values:
  ,s_uri
  ,s_blank
  ,p_uri
  ,o_uri
  ,o_blank
  ,o_text
  ,o_language
  ,o_datatype
  ,c_uri
FROM triples
WHERE
 -- subject
    ((2665414293436938691   IS NULL) OR (s_uri_id   = 2665414293436938691))
AND ((NULL IS NULL) OR (s_blank_id = NULL))
AND ((NULL   IS NULL) OR (p_uri_id   = NULL))
 -- object
AND ((NULL   IS NULL) OR (o_uri_id   = NULL))
AND ((NULL IS NULL) OR (o_blank_id = NULL))
AND ((NULL   IS NULL) OR (o_lit_id   = NULL))
 -- context node
AND ((NULL   IS NULL) OR (c_uri_id   = NULL))

librdf.sqlite.diff

diff --git a/rdf_storage_sqlite_mro.c b/rdf_storage_sqlite_mro.c
index 8fc6f27..20cd612 100644
--- a/rdf_storage_sqlite_mro.c
+++ b/rdf_storage_sqlite_mro.c
@@ -761,8 +761,11 @@ static int pub_open(librdf_storage *storage, librdf_model *model)
         }
 
         // http://stackoverflow.com/a/6618833
-        // sqlite3_profile(db_ctx->db, &profile, NULL);
-        // sqlite3_profile(db_ctx->db, &trace, NULL);
+
+        fprintf(stderr, "Setting profile and trace.\n");
+
+        sqlite3_profile(db_ctx->db, &profile, NULL);
+        sqlite3_trace(db_ctx->db, &trace, NULL);
     }
 
     // set DB session PRAGMAs
@@ -1268,12 +1271,12 @@ static librdf_stream *pub_context_find_statements(librdf_storage *storage, librd
     sqlite3_stmt *stmt = NULL;
     stmt = prep_stmt(db_ctx->db, &stmt, find_triples_sql);
 
-    // librdf_log( librdf_storage_get_world(storage), 0, LIBRDF_LOG_INFO, LIBRDF_FROM_STORAGE, NULL, "%s", librdf_statement_to_string(statement) );
+    librdf_log( librdf_storage_get_world(storage), 0, LIBRDF_LOG_INFO, LIBRDF_FROM_STORAGE, NULL, "%s", librdf_statement_to_string(statement) );
 
     const sqlite_rc_t rc = bind_stmt(db_ctx, statement, context_node, stmt);
     assert(SQLITE_OK == rc && "foo");
 
-    // printExplainQueryPlan(stmt);
+    printExplainQueryPlan(stmt);
 
     librdf_world *w = get_world(storage);
     // create iterator

slow_describe_query.log

Query: BEGIN IMMEDIATE TRANSACTION;
Query: BEGIN IMMEDIATE TRANSACTION;
Execution Time: 0 ms
librdf info - <http://www.openbel.org/bel/namespace/entrez-gene> (null) (null)
0 0 0 SCAN TABLE triple_relations  -- result columns must match as in enum idx_triple_column_t
SELECT
 -- all *_id (hashes):
  id
  ,s_uri_id
  ,s_blank_id
  ,p_uri_id
  ,o_uri_id
  ,o_blank_id
  ,o_lit_id
  ,o_datatype_id
  ,c_uri_id
 -- all values:
  ,s_uri
  ,s_blank
  ,p_uri
  ,o_uri
  ,o_blank
  ,o_text
  ,o_language
  ,o_datatype
  ,c_uri
FROM triples
WHERE
 -- subject
    ((:s_uri_id   IS NULL) OR (s_uri_id   = :s_uri_id))
AND ((:s_blank_id IS NULL) OR (s_blank_id = :s_blank_id))
AND ((:p_uri_id   IS NULL) OR (p_uri_id   = :p_uri_id))
 -- object
AND ((:o_uri_id   IS NULL) OR (o_uri_id   = :o_uri_id))
AND ((:o_blank_id IS NULL) OR (o_blank_id = :o_blank_id))
AND ((:o_lit_id   IS NULL) OR (o_lit_id   = :o_lit_id))
 -- context node
AND ((:c_uri_id   IS NULL) OR (c_uri_id   = :c_uri_id))
 
0 1 1 SEARCH TABLE so_uris AS s_uris USING INTEGER PRIMARY KEY (rowid=?)  -- result columns must match as in enum idx_triple_column_t
SELECT
 -- all *_id (hashes):
  id
  ,s_uri_id
  ,s_blank_id
  ,p_uri_id
  ,o_uri_id
  ,o_blank_id
  ,o_lit_id
  ,o_datatype_id
  ,c_uri_id
 -- all values:
  ,s_uri
  ,s_blank
  ,p_uri
  ,o_uri
  ,o_blank
  ,o_text
  ,o_language
  ,o_datatype
  ,c_uri
FROM triples
WHERE
 -- subject
    ((:s_uri_id   IS NULL) OR (s_uri_id   = :s_uri_id))
AND ((:s_blank_id IS NULL) OR (s_blank_id = :s_blank_id))
AND ((:p_uri_id   IS NULL) OR (p_uri_id   = :p_uri_id))
 -- object
AND ((:o_uri_id   IS NULL) OR (o_uri_id   = :o_uri_id))
AND ((:o_blank_id IS NULL) OR (o_blank_id = :o_blank_id))
AND ((:o_lit_id   IS NULL) OR (o_lit_id   = :o_lit_id))
 -- context node
AND ((:c_uri_id   IS NULL) OR (c_uri_id   = :c_uri_id))
 
0 2 2 SEARCH TABLE so_blanks AS s_blanks USING INTEGER PRIMARY KEY (rowid=?)  -- result columns must match as in enum idx_triple_column_t
SELECT
 -- all *_id (hashes):
  id
  ,s_uri_id
  ,s_blank_id
  ,p_uri_id
  ,o_uri_id
  ,o_blank_id
  ,o_lit_id
  ,o_datatype_id
  ,c_uri_id
 -- all values:
  ,s_uri
  ,s_blank
  ,p_uri
  ,o_uri
  ,o_blank
  ,o_text
  ,o_language
  ,o_datatype
  ,c_uri
FROM triples
WHERE
 -- subject
    ((:s_uri_id   IS NULL) OR (s_uri_id   = :s_uri_id))
AND ((:s_blank_id IS NULL) OR (s_blank_id = :s_blank_id))
AND ((:p_uri_id   IS NULL) OR (p_uri_id   = :p_uri_id))
 -- object
AND ((:o_uri_id   IS NULL) OR (o_uri_id   = :o_uri_id))
AND ((:o_blank_id IS NULL) OR (o_blank_id = :o_blank_id))
AND ((:o_lit_id   IS NULL) OR (o_lit_id   = :o_lit_id))
 -- context node
AND ((:c_uri_id   IS NULL) OR (c_uri_id   = :c_uri_id))
 
0 3 3 SEARCH TABLE p_uris AS p_uris USING INTEGER PRIMARY KEY (rowid=?)  -- result columns must match as in enum idx_triple_column_t
SELECT
 -- all *_id (hashes):
  id
  ,s_uri_id
  ,s_blank_id
  ,p_uri_id
  ,o_uri_id
  ,o_blank_id
  ,o_lit_id
  ,o_datatype_id
  ,c_uri_id
 -- all values:
  ,s_uri
  ,s_blank
  ,p_uri
  ,o_uri
  ,o_blank
  ,o_text
  ,o_language
  ,o_datatype
  ,c_uri
FROM triples
WHERE
 -- subject
    ((:s_uri_id   IS NULL) OR (s_uri_id   = :s_uri_id))
AND ((:s_blank_id IS NULL) OR (s_blank_id = :s_blank_id))
AND ((:p_uri_id   IS NULL) OR (p_uri_id   = :p_uri_id))
 -- object
AND ((:o_uri_id   IS NULL) OR (o_uri_id   = :o_uri_id))
AND ((:o_blank_id IS NULL) OR (o_blank_id = :o_blank_id))
AND ((:o_lit_id   IS NULL) OR (o_lit_id   = :o_lit_id))
 -- context node
AND ((:c_uri_id   IS NULL) OR (c_uri_id   = :c_uri_id))
 
0 4 4 SEARCH TABLE so_uris AS o_uris USING INTEGER PRIMARY KEY (rowid=?)  -- result columns must match as in enum idx_triple_column_t
SELECT
 -- all *_id (hashes):
  id
  ,s_uri_id
  ,s_blank_id
  ,p_uri_id
  ,o_uri_id
  ,o_blank_id
  ,o_lit_id
  ,o_datatype_id
  ,c_uri_id
 -- all values:
  ,s_uri
  ,s_blank
  ,p_uri
  ,o_uri
  ,o_blank
  ,o_text
  ,o_language
  ,o_datatype
  ,c_uri
FROM triples
WHERE
 -- subject
    ((:s_uri_id   IS NULL) OR (s_uri_id   = :s_uri_id))
AND ((:s_blank_id IS NULL) OR (s_blank_id = :s_blank_id))
AND ((:p_uri_id   IS NULL) OR (p_uri_id   = :p_uri_id))
 -- object
AND ((:o_uri_id   IS NULL) OR (o_uri_id   = :o_uri_id))
AND ((:o_blank_id IS NULL) OR (o_blank_id = :o_blank_id))
AND ((:o_lit_id   IS NULL) OR (o_lit_id   = :o_lit_id))
 -- context node
AND ((:c_uri_id   IS NULL) OR (c_uri_id   = :c_uri_id))
 
0 5 5 SEARCH TABLE so_blanks AS o_blanks USING INTEGER PRIMARY KEY (rowid=?)  -- result columns must match as in enum idx_triple_column_t
SELECT
 -- all *_id (hashes):
  id
  ,s_uri_id
  ,s_blank_id
  ,p_uri_id
  ,o_uri_id
  ,o_blank_id
  ,o_lit_id
  ,o_datatype_id
  ,c_uri_id
 -- all values:
  ,s_uri
  ,s_blank
  ,p_uri
  ,o_uri
  ,o_blank
  ,o_text
  ,o_language
  ,o_datatype
  ,c_uri
FROM triples
WHERE
 -- subject
    ((:s_uri_id   IS NULL) OR (s_uri_id   = :s_uri_id))
AND ((:s_blank_id IS NULL) OR (s_blank_id = :s_blank_id))
AND ((:p_uri_id   IS NULL) OR (p_uri_id   = :p_uri_id))
 -- object
AND ((:o_uri_id   IS NULL) OR (o_uri_id   = :o_uri_id))
AND ((:o_blank_id IS NULL) OR (o_blank_id = :o_blank_id))
AND ((:o_lit_id   IS NULL) OR (o_lit_id   = :o_lit_id))
 -- context node
AND ((:c_uri_id   IS NULL) OR (c_uri_id   = :c_uri_id))
 
0 6 6 SEARCH TABLE o_literals AS o_literals USING INTEGER PRIMARY KEY (rowid=?)  -- result columns must match as in enum idx_triple_column_t
SELECT
 -- all *_id (hashes):
  id
  ,s_uri_id
  ,s_blank_id
  ,p_uri_id
  ,o_uri_id
  ,o_blank_id
  ,o_lit_id
  ,o_datatype_id
  ,c_uri_id
 -- all values:
  ,s_uri
  ,s_blank
  ,p_uri
  ,o_uri
  ,o_blank
  ,o_text
  ,o_language
  ,o_datatype
  ,c_uri
FROM triples
WHERE
 -- subject
    ((:s_uri_id   IS NULL) OR (s_uri_id   = :s_uri_id))
AND ((:s_blank_id IS NULL) OR (s_blank_id = :s_blank_id))
AND ((:p_uri_id   IS NULL) OR (p_uri_id   = :p_uri_id))
 -- object
AND ((:o_uri_id   IS NULL) OR (o_uri_id   = :o_uri_id))
AND ((:o_blank_id IS NULL) OR (o_blank_id = :o_blank_id))
AND ((:o_lit_id   IS NULL) OR (o_lit_id   = :o_lit_id))
 -- context node
AND ((:c_uri_id   IS NULL) OR (c_uri_id   = :c_uri_id))
 
0 7 7 SEARCH TABLE t_uris AS o_lit_uris USING INTEGER PRIMARY KEY (rowid=?)  -- result columns must match as in enum idx_triple_column_t
SELECT
 -- all *_id (hashes):
  id
  ,s_uri_id
  ,s_blank_id
  ,p_uri_id
  ,o_uri_id
  ,o_blank_id
  ,o_lit_id
  ,o_datatype_id
  ,c_uri_id
 -- all values:
  ,s_uri
  ,s_blank
  ,p_uri
  ,o_uri
  ,o_blank
  ,o_text
  ,o_language
  ,o_datatype
  ,c_uri
FROM triples
WHERE
 -- subject
    ((:s_uri_id   IS NULL) OR (s_uri_id   = :s_uri_id))
AND ((:s_blank_id IS NULL) OR (s_blank_id = :s_blank_id))
AND ((:p_uri_id   IS NULL) OR (p_uri_id   = :p_uri_id))
 -- object
AND ((:o_uri_id   IS NULL) OR (o_uri_id   = :o_uri_id))
AND ((:o_blank_id IS NULL) OR (o_blank_id = :o_blank_id))
AND ((:o_lit_id   IS NULL) OR (o_lit_id   = :o_lit_id))
 -- context node
AND ((:c_uri_id   IS NULL) OR (c_uri_id   = :c_uri_id))
 
0 8 8 SEARCH TABLE c_uris AS c_uris USING INTEGER PRIMARY KEY (rowid=?)  -- result columns must match as in enum idx_triple_column_t
SELECT
 -- all *_id (hashes):
  id
  ,s_uri_id
  ,s_blank_id
  ,p_uri_id
  ,o_uri_id
  ,o_blank_id
  ,o_lit_id
  ,o_datatype_id
  ,c_uri_id
 -- all values:
  ,s_uri
  ,s_blank
  ,p_uri
  ,o_uri
  ,o_blank
  ,o_text
  ,o_language
  ,o_datatype
  ,c_uri
FROM triples
WHERE
 -- subject
    ((:s_uri_id   IS NULL) OR (s_uri_id   = :s_uri_id))
AND ((:s_blank_id IS NULL) OR (s_blank_id = :s_blank_id))
AND ((:p_uri_id   IS NULL) OR (p_uri_id   = :p_uri_id))
 -- object
AND ((:o_uri_id   IS NULL) OR (o_uri_id   = :o_uri_id))
AND ((:o_blank_id IS NULL) OR (o_blank_id = :o_blank_id))
AND ((:o_lit_id   IS NULL) OR (o_lit_id   = :o_lit_id))
 -- context node
AND ((:c_uri_id   IS NULL) OR (c_uri_id   = :c_uri_id))
 
Query: EXPLAIN QUERY PLAN  -- result columns must match as in enum idx_triple_column_t
SELECT
 -- all *_id (hashes):
  id
  ,s_uri_id
  ,s_blank_id
  ,p_uri_id
  ,o_uri_id
  ,o_blank_id
  ,o_lit_id
  ,o_datatype_id
  ,c_uri_id
 -- all values:
  ,s_uri
  ,s_blank
  ,p_uri
  ,o_uri
  ,o_blank
  ,o_text
  ,o_language
  ,o_datatype
  ,c_uri
FROM triples
WHERE
 -- subject
    ((:s_uri_id   IS NULL) OR (s_uri_id   = :s_uri_id))
AND ((:s_blank_id IS NULL) OR (s_blank_id = :s_blank_id))
AND ((:p_uri_id   IS NULL) OR (p_uri_id   = :p_uri_id))
 -- object
AND ((:o_uri_id   IS NULL) OR (o_uri_id   = :o_uri_id))
AND ((:o_blank_id IS NULL) OR (o_blank_id = :o_blank_id))
AND ((:o_lit_id   IS NULL) OR (o_lit_id   = :o_lit_id))
 -- context node
AND ((:c_uri_id   IS NULL) OR (c_uri_id   = :c_uri_id))

Execution Time: 1 ms
Query:  -- result columns must match as in enum idx_triple_column_t
SELECT
 -- all *_id (hashes):
  id
  ,s_uri_id
  ,s_blank_id
  ,p_uri_id
  ,o_uri_id
  ,o_blank_id
  ,o_lit_id
  ,o_datatype_id
  ,c_uri_id
 -- all values:
  ,s_uri
  ,s_blank
  ,p_uri
  ,o_uri
  ,o_blank
  ,o_text
  ,o_language
  ,o_datatype
  ,c_uri
FROM triples
WHERE
 -- subject
    ((2665414293436938691   IS NULL) OR (s_uri_id   = 2665414293436938691))
AND ((NULL IS NULL) OR (s_blank_id = NULL))
AND ((NULL   IS NULL) OR (p_uri_id   = NULL))
 -- object
AND ((NULL   IS NULL) OR (o_uri_id   = NULL))
AND ((NULL IS NULL) OR (o_blank_id = NULL))
AND ((NULL   IS NULL) OR (o_lit_id   = NULL))
 -- context node
AND ((NULL   IS NULL) OR (c_uri_id   = NULL))

Query:  -- result columns must match as in enum idx_triple_column_t
SELECT
 -- all *_id (hashes):
  id
  ,s_uri_id
  ,s_blank_id
  ,p_uri_id
  ,o_uri_id
  ,o_blank_id
  ,o_lit_id
  ,o_datatype_id
  ,c_uri_id
 -- all values:
  ,s_uri
  ,s_blank
  ,p_uri
  ,o_uri
  ,o_blank
  ,o_text
  ,o_language
  ,o_datatype
  ,c_uri
FROM triples
WHERE
 -- subject
    ((:s_uri_id   IS NULL) OR (s_uri_id   = :s_uri_id))
AND ((:s_blank_id IS NULL) OR (s_blank_id = :s_blank_id))
AND ((:p_uri_id   IS NULL) OR (p_uri_id   = :p_uri_id))
 -- object
AND ((:o_uri_id   IS NULL) OR (o_uri_id   = :o_uri_id))
AND ((:o_blank_id IS NULL) OR (o_blank_id = :o_blank_id))
AND ((:o_lit_id   IS NULL) OR (o_lit_id   = :o_lit_id))
 -- context node
AND ((:c_uri_id   IS NULL) OR (c_uri_id   = :c_uri_id))

Execution Time: 63409 ms
Query: ROLLBACK TRANSACTION;
Query: ROLLBACK TRANSACTION;
Execution Time: 0 ms

sqlite3_log.sql

SQLite version 3.9.1 2015-10-16 17:31:12
Enter ".help" for usage hints.
sqlite> -- First enable the timer and explain query plan config.
sqlite> .timer on
sqlite> .eqp on
sqlite> -- Now we'll try executing the query posed by librdf.sqlite for a subject basic-graph-pattern by URI (i.e. DESCRIBE URI).
sqlite> select * from triples where ((2665414293436938691 is NULL) OR (s_uri_id = 2665414293436938691)) AND ((NULL IS NULL) OR (s_blank_id = NULL)) AND ((NULL IS NULL) OR (p_uri_id = NULL)) AND ((NULL IS NULL) OR (o_uri_id = NULL)) AND ((NULL IS NULL) OR (o_blank_id = NULL)) AND ((NULL IS NULL) OR (o_lit_id = NULL)) AND ((NULL IS NULL) OR (c_uri_id = NULL));
--EQP-- 0,0,0,SCAN TABLE triple_relations
--EQP-- 0,1,1,SEARCH TABLE so_uris AS s_uris USING INTEGER PRIMARY KEY (rowid=?)
--EQP-- 0,2,2,SEARCH TABLE so_blanks AS s_blanks USING INTEGER PRIMARY KEY (rowid=?)
--EQP-- 0,3,3,SEARCH TABLE p_uris AS p_uris USING INTEGER PRIMARY KEY (rowid=?)
--EQP-- 0,4,4,SEARCH TABLE so_uris AS o_uris USING INTEGER PRIMARY KEY (rowid=?)
--EQP-- 0,5,5,SEARCH TABLE so_blanks AS o_blanks USING INTEGER PRIMARY KEY (rowid=?)
--EQP-- 0,6,6,SEARCH TABLE o_literals AS o_literals USING INTEGER PRIMARY KEY (rowid=?)
--EQP-- 0,7,7,SEARCH TABLE t_uris AS o_lit_uris USING INTEGER PRIMARY KEY (rowid=?)
--EQP-- 0,8,8,SEARCH TABLE c_uris AS c_uris USING INTEGER PRIMARY KEY (rowid=?)
-6595571342609273112|2665414293436938691||-4913921494813720685|||-4032604740825980284|-7210322708539680434||http://www.openbel.org/bel/namespace/entrez-gene||http://www.openbel.org/vocabulary/domain|||gene and gene product||http://www.w3.org/2001/XMLSchema#string|
-1459561902396590814|2665414293436938691||-1152730897121068147|||7971336031732848173|-7210322708539680434||http://www.openbel.org/bel/namespace/entrez-gene||http://www.w3.org/2004/02/skos/core#prefLabel|||Entrez Gene||http://www.w3.org/2001/XMLSchema#string|
-1027106250966710582|2665414293436938691||8891365258715654442|||-7539692296793019894|-7210322708539680434||http://www.openbel.org/bel/namespace/entrez-gene||http://www.openbel.org/vocabulary/prefix|||egid||http://www.w3.org/2001/XMLSchema#string|
4937578649821701695|2665414293436938691||-8800921675913015609|5685737804649325757|||||http://www.openbel.org/bel/namespace/entrez-gene||http://www.w3.org/1999/02/22-rdf-syntax-ns#type|http://www.openbel.org/vocabulary/NamespaceConceptScheme|||||
9219966604398637134|2665414293436938691||-8800921675913015609|-8189317538167393874|||||http://www.openbel.org/bel/namespace/entrez-gene||http://www.w3.org/1999/02/22-rdf-syntax-ns#type|http://www.w3.org/2004/02/skos/core#ConceptScheme|||||
Run Time: real 3.015 user 2.226666 sys 0.790000
sqlite> -- The query takes 3 seconds to complete due to a table scan on triples. The table scan seems to be required for the "(2665414293436938691 is NULL)" clause. When this is removed the executime time goes to .002, three orders of magnitude faster.
sqlite>