Get execution plan of dynamic query

From: Алексей Ш(dot) <savbr(at)rin(dot)ru>
To: pgsql-general(at)postgresql(dot)org
Subject: Get execution plan of dynamic query
Date: 2005-01-07 11:29:00
Message-ID: 200501071527.j07FR1dO019407@mirror-02.rin.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ronnie Meier Ramos 2005-01-07 11:33:59 Re: Global/persistent variables
Previous Message Tino Wildenhain 2005-01-07 11:20:13 Re: Books for experienced DB developer