EXPLAIN ANALYZE within PL/pgSQL gives error: "query has no destination for result data"

Question

I am trying to understand the query plan for a select statement within a PL/pgSQL function, but I keep getting errors. My question: how do I get the query plan?

Following is a simple case that reproduces the problem.

The table in question is named test_table.

CREATE TABLE test_table
(
  name character varying,
  id integer
);

The function is as follows:

DROP FUNCTION IF EXISTS test_function_1(INTEGER);
CREATE OR REPLACE FUNCTION test_function_1(inId INTEGER) 
RETURNS TABLE(outName varchar)
AS 
$$
BEGIN
  -- is there a way to get the explain analyze output?
  explain analyze select t.name from test_table t where t.id = inId;

  -- return query select t.name from test_table t where t.id = inId;
END;
$$ LANGUAGE plpgsql;

When I run

select * from test_function_1(10);

I get the error:

ERROR:  query has no destination for result data
CONTEXT:  PL/pgSQL function test_function_1(integer) line 3 at SQL statement

The function works fine if I uncomment the commented portion and comment out explain analyze.

Lozoya

Answer 1

Or you can use this simpler form with RETURN QUERY:

CREATE OR REPLACE FUNCTION f_explain_analyze(int)
  RETURNS SETOF text
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY
   EXPLAIN ANALYZE SELECT * FROM foo WHERE v = $1;
END
$func$;

Call:

SELECT * FROM f_explain_analyze(1);

Stuff

This looks like a really nice solution for trouble shooting and developing new functions. You can have more than one RETURN QUERY in your function which if you had function with 10 queries you could tack on a RETURN QUERY EXPLAIN ANALYZE and get a explain back for each one. Nice! – Stonedeaf

Answer 2

Any query has to have a known target in plpgsql (or you can throw the result away with a PERFORM statement). So you can do:

CREATE OR REPLACE FUNCTION fx(text)
RETURNS void AS $$
DECLARE t text;
BEGIN
  FOR t IN EXPLAIN ANALYZE SELECT * FROM foo WHERE v = $1
  LOOP
    RAISE NOTICE '%', t;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

postgres=# SELECT fx('1');
NOTICE:  Seq Scan on foo  (cost=0.00..1.18 rows=1 width=3) (actual time=0.024..0.024 rows=0 loops=1)
NOTICE:    Filter: ((v)::text = '1'::text)
NOTICE:    Rows Removed by Filter: 14
NOTICE:  Planning time: 0.103 ms
NOTICE:  Total runtime: 0.065 ms
 fx 
────

(1 row)

Another possibility to get the plan for embedded SQL is using a prepared statement:

postgres=# PREPARE xx(text) AS SELECT * FROM foo WHERE v = $1;
PREPARE
Time: 0.810 ms

postgres=# EXPLAIN ANALYZE EXECUTE xx('1');
                                         QUERY PLAN                                          
─────────────────────────────────────────────────────────────────────────────────────────────
 Seq Scan on foo  (cost=0.00..1.18 rows=1 width=3) (actual time=0.030..0.030 rows=0 loops=1)
   Filter: ((v)::text = '1'::text)
   Rows Removed by Filter: 14
 Total runtime: 0.083 ms
(4 rows)

Nonunionism

Answer 3

You could take a look at http://www.postgresql.org/docs/current/static/auto-explain.html and capture the explain in the log file.

Also see if this does what you want. https://github.com/pgexperts/explanation

Stonedeaf

This (auto_explain) works, but involves a bit more effort than the accepted solution in my case. Thanks for your reply (don't know why you were downvoted!). – Lozoya