SPARQL Query Debugger — EXPLAIN SPARQL

pg_ripple v0.50.0 extends pg_ripple.explain_sparql() with an interactive query debugger mode (analyze := true) that surfaces the algebra tree, generated SQL, plan-cache status, and per-operator row counts as a structured JSONB document.

Function Signature

pg_ripple.explain_sparql(
    query       TEXT,
    analyze     BOOL DEFAULT FALSE
) RETURNS JSONB

Output Schema

cache_status values

actual_rows

Only present when analyze := true. Contains a flat array of actual row counts extracted from PostgreSQL's EXPLAIN ANALYZE JSON output — one integer per plan node, in plan-tree order.

Examples

Basic explain (no execution)

SELECT pg_ripple.explain_sparql(
    'SELECT ?name WHERE { ?s <http://schema.org/name> ?name }',
    false
) AS explain_out;

Interactive debugger (with row counts)

WITH result AS (
    SELECT pg_ripple.explain_sparql(
        'SELECT ?name WHERE { ?s <http://schema.org/name> ?name }',
        true
    ) AS j
)
SELECT
    j->>'cache_status'  AS cache_status,
    j->>'sql'           AS generated_sql,
    j->'actual_rows'    AS actual_rows_per_operator
FROM result;

All four query types

-- SELECT
SELECT pg_ripple.explain_sparql('SELECT * WHERE { ?s ?p ?o } LIMIT 5', true);

-- ASK
SELECT pg_ripple.explain_sparql('ASK { <http://example.org/a> ?p ?o }', true);

-- CONSTRUCT
SELECT pg_ripple.explain_sparql(
    'CONSTRUCT { ?s ?p ?o } WHERE { ?s ?p ?o } LIMIT 5', true);

-- DESCRIBE (returns algebra + synthetic SQL stub; no error)
SELECT pg_ripple.explain_sparql('DESCRIBE <http://example.org/a>', false);

Interpreting the Algebra Tree

The algebra field is the string representation of the internal spargebra algebra tree. It shows how the SPARQL engine parsed and structured your query before SQL generation. Common nodes:

• Project — variable projection
• Filter — FILTER expression
• Join / LeftJoin — triple-pattern joins / OPTIONAL
• BGP — basic graph pattern (list of triple patterns)
• Union — UNION clause
• Extend — BIND expression

Tuning with EXPLAIN ANALYZE

Use actual_rows to compare estimated vs actual row counts:

WITH debug AS (
    SELECT pg_ripple.explain_sparql(
        'SELECT ?s WHERE { ?s <http://schema.org/type> <http://schema.org/Person> }',
        true
    ) AS j
)
SELECT
    jsonb_array_length(j->'actual_rows') AS num_plan_nodes,
    j->'actual_rows'                      AS row_counts
FROM debug;

If row estimates are far from actuals, run ANALYZE on the VP tables:

ANALYZE _pg_ripple.vp_rare;

Configuration