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 by watching your logs for slow queries (according to
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; 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.Proceed to Step 2: Configure collector
Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →