EXPLAIN plan permissions for PostgreSQL monitoring

Preview

We're still working on this feature, but we'd love for you to try it out!

This feature is currently provided as part of a preview pursuant to our pre-release policies.

By default, EXPLAIN runs directly as the monitoring user, and PostgreSQL checks table privileges at plan time — so row-locking or write statements fail with permission denied unless the monitoring user has write access, which it should never have.

Create the EXPLAIN helper function

To collect query plans for locking or write statements without granting write access to the monitoring user, create the following function in each database:

CREATE OR REPLACE FUNCTION otel.explain_statement(l_query text)
RETURNS json
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    v_plan json;
    v_param_count int;
    v_nulls text := '';
    i int;
BEGIN
    SET TRANSACTION READ ONLY;
    SET plan_cache_mode = force_generic_plan;

    EXECUTE 'PREPARE otel_explain_stmt AS ' || l_query;

    SELECT COALESCE(array_length(parameter_types, 1), 0) INTO v_param_count
    FROM pg_prepared_statements WHERE name = 'otel_explain_stmt';

    IF v_param_count > 0 THEN
        FOR i IN 1..v_param_count LOOP
            v_nulls := v_nulls || CASE WHEN i > 1 THEN ', ' ELSE '' END || 'null';
        END LOOP;
        v_nulls := '(' || v_nulls || ')';
    END IF;

    EXECUTE 'EXPLAIN (FORMAT JSON) EXECUTE otel_explain_stmt' || v_nulls INTO v_plan;
    DEALLOCATE otel_explain_stmt;
    RETURN v_plan;
EXCEPTION WHEN OTHERS THEN
    IF EXISTS (SELECT 1 FROM pg_prepared_statements WHERE name = 'otel_explain_stmt') THEN
        DEALLOCATE otel_explain_stmt;
    END IF;
    RAISE;
END;
$$;

GRANT EXECUTE ON FUNCTION otel.explain_statement(text) TO <YOUR_DB_USERNAME>;

Execution plan collection mechanics

Rate limiting: top_query_collection.max_explain_each_interval (default 1000) caps EXPLAINs per scrape.
Caching: query_plan_cache_size / query_plan_cache_ttl (default 1000 entries / 1h) cache a plan once obtained, keyed by query ID.

Instrumentation in self-hosted environments

Learn how to set up PostgreSQL monitoring in self-hosted environments with New Relic.

Metrics reference

Learn about the available metrics collected by the NRDOT Collector.

Troubleshooting guide

Learn how to troubleshoot common issues with PostgreSQL monitoring.