Recipes

Two stock PostgreSQL extensions that are useful alongside pgRDF. Neither is required.

pg_prewarm: warm the dictionary and quads

pg_prewarm ships with PostgreSQL. After a restart, pgRDF's dictionary and quad partitions are cold, and the first queries pay for reading pages from disk. Loading the relations first gives a stable baseline for benchmarks and a warm start for steady workloads.

CREATE EXTENSION IF NOT EXISTS pg_prewarm;

-- The dictionary: heap and indexes.
SELECT pg_prewarm('pgrdf._pgrdf_dictionary');
SELECT pg_prewarm('pgrdf._pgrdf_dictionary_pkey');
SELECT pg_prewarm('pgrdf.unique_term');
SELECT pg_prewarm('pgrdf._pgrdf_dict_val_idx');

-- _pgrdf_quads is partitioned, so the parent has no storage of its own.
-- Warm every partition's heap, then every partition's indexes.
SELECT count(pg_prewarm(c.oid::regclass))
  FROM pg_inherits i
  JOIN pg_class c ON c.oid = i.inhrelid
 WHERE i.inhparent = 'pgrdf._pgrdf_quads'::regclass;

SELECT count(pg_prewarm(ix.indexrelid::regclass))
  FROM pg_inherits i
  JOIN pg_index ix ON ix.indrelid = i.inhrelid
 WHERE i.inhparent = 'pgrdf._pgrdf_quads'::regclass;

pg_prewarm(relation, mode) takes an optional mode: 'buffer' (the default) loads pages into shared_buffers, 'read' reads them so the operating system caches them, and 'prefetch' issues asynchronous read-ahead hints.

The quad indexes are declared on the parent as _pgrdf_idx_spo, _pgrdf_idx_pos and _pgrdf_idx_osp. Each partition's copies have generated names (for example _pgrdf_quads_g1_subject_id_predicate_id_object_id_is_inferr_idx), which is why the query above finds them through pg_inherits rather than by name.

When to use it. Before timing queries, so you measure the warm path; and after a restart, when the production workload is normally warm.

When not to bother. Small graphs, which the first query warms anyway, and servers whose shared_buffers is much smaller than the quad tables and indexes. There, prewarming evicts pages as fast as it loads them.

Caveat. Prewarming does not survive a restart. For automatic reloading, see pg_prewarm.autoprewarm in the PostgreSQL documentation.

pg_stat_statements: observe translated SQL

pgRDF translates each SPARQL query into one SQL statement with the dictionary ids of its constants passed as $N parameters (see Query). Queries of the same shape therefore produce the same statement text, whatever IRIs and literals they name, and pg_stat_statements aggregates them into one row.

# postgresql.conf, then restart
shared_preload_libraries = 'pgrdf,pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max   = 10000

track = all is required. pgRDF runs the translated SQL through SPI, as a statement nested inside your SELECT … FROM pgrdf.sparql(…) call. With the default track = top, only the outer call is recorded.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- After a workload run:
SELECT calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 3)  AS mean_ms,
       rows,
       left(query, 120)                   AS query_prefix
  FROM pg_stat_statements
 WHERE query ILIKE '%_pgrdf_quads%'
    OR query ILIKE '%_pgrdf_dictionary%'
 ORDER BY total_exec_time DESC
 LIMIT 20;

What you see. One row per query shape, with calls counting every execution of that shape. Pair it with the plan-cache counters in pgrdf.stats() (plan_cache_hits, plan_cache_misses): a shape that is called often but keeps missing the plan cache points to something invalidating the prepared plan.

Caveats.

• pg_stat_statements.max defaults to 5000. A workload with many distinct query shapes can fill it; raise it as needed.
• The translated SQL, and therefore the statement text and query id, can change between pgRDF releases. Do not compare statement ids across an upgrade; check the changelog first.
• Both extensions must be in shared_preload_libraries; see Packaging.