Step 1: Create helper functions

The log-based EXPLAIN mechanism is an alternative to the auto_explain module for EXPLAIN plan collection. We generally recommend using auto_explain to collect EXPLAIN plans automatically, but not all environments support the auto_explain module (bundled with Postgres), so we offer an alternative mechanism.

The log-based EXPLAIN mechanism works by watching your logs for slow queries (according to log_min_duration_statement) and running EXPLAIN for each one. This has some limitations (most notably, we only run EXPLAIN, not EXPLAIN ANALYZE), but still provides valuable insights. Learn more in our Log-based EXPLAIN overview.

In order to use log-based EXPLAIN, the collector needs permissions to run EXPLAIN queries on your database. The safest way to permit this is to create the following helper function in each database you want to monitor:

CREATE SCHEMA IF NOT EXISTS pganalyze;
GRANT USAGE ON SCHEMA pganalyze TO pganalyze;
CREATE OR REPLACE FUNCTION pganalyze.explain(query text, params text[]) RETURNS text AS
$$
DECLARE
  prepared_query text;
  prepared_params text;
  result text;
BEGIN
  SELECT regexp_replace(query, ';+\s*\Z', '') INTO prepared_query;
  IF prepared_query LIKE '%;%' THEN
    RAISE EXCEPTION 'cannot run EXPLAIN when query contains semicolon';
  END IF;

  IF array_length(params, 1) > 0 THEN
    SELECT string_agg(quote_literal(param) || '::unknown', ',') FROM unnest(params) p(param) INTO prepared_params;

    EXECUTE 'PREPARE pganalyze_explain AS ' || prepared_query;
    BEGIN
      EXECUTE 'EXPLAIN (VERBOSE, FORMAT JSON) EXECUTE pganalyze_explain(' || prepared_params || ')' INTO STRICT result;
    EXCEPTION WHEN OTHERS THEN
      DEALLOCATE pganalyze_explain;
      RAISE;
    END;
    DEALLOCATE pganalyze_explain;
  ELSE
    EXECUTE 'EXPLAIN (VERBOSE, FORMAT JSON) ' || prepared_query INTO STRICT result;
  END IF;

  RETURN result;
END
$$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

Note that this function contains a check for semicolons in the query text. This is to minimize collector access to your data: it ensures that the collector cannot piggyback other queries that could exfiltrate data.

Because EXPLAIN needs to run in the same database where the query ran, you will need to create this function in each database you want to monitor. You should create the function as a Postgres superuser to ensure EXPLAIN access to all queries.