Check out our free resources and eBooks like: "Effective Indexing in Postgres"

Step 1: Create helper functions

We generally recommend using auto_explain to collect EXPLAIN plans automatically, but Crunchy Bridge does not yet support adding the required auto_explain module to shared_preload_libraries, so we offer an alternative, log-based, EXPLAIN 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.

CREATE SCHEMA IF NOT EXISTS pganalyze;
GRANT USAGE ON SCHEMA pganalyze TO application;
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 →