Get execution plan of dynamic query
How to get results of EXPLAIN of dynamic query maked up in PL/PGSQL function?
I found option 'debug_print_plan', but it produces incomprehensible output. Is there an option to dump execution plan in EXPLAIN format?
EXPLAIN return generic result set in client application, as 'SELECT' command do. But in PL/PGSQL function
FOR rec IN EXPLAIN query_text LOOP ...
and
FOR rec IN EXECUTE('EXPLAIN '||query_text) LOOP ...
both failed with error "cannot open non-SELECT query as cursor".
I can receive EXPLAIN results through
select * from dblink('EXPLAIN '||query_text) (query_plan text)
but it doesn't work if queries use temporary tables.
PL/Perl function spi_exec_query('EXPLAIN select ...') returns no rows and status=SPI_OK_UTILITY (PostgreSQL 8.0.0rc1 on i686-pc-mingw32).
Is it another way?
"=?KOI8-R?Q?=E1=CC=C5=CB=D3=C5=CA =FB.?=" <savbr@rin.ru> writes:
How to get results of EXPLAIN of dynamic query maked up in PL/PGSQL function?
Pretend that it's a prepared statement.
For example, if your plpgsql function has
declare x int;
y int;
begin
...
select f1 into x from tab1 where f2 = y;
then this will show the same plan as plpgsql will be using:
prepare foo(int) as select f1 from tab1 where f2 = $1;
explain analyze execute foo(42);
regards, tom lane