Getting

[Dan Ng]

Hello,
I am a newb in Postgres coming from Oracle. Does anyone know if it's possible to see the plan being executed by Postgres for an active sql statement (from pg_stat_activity). This is to be able to troubleshoot poor performing ones and in Oracle we are able to retrieve such a plan.
Additionally, how do I see the actual sql statement that shows as "<unnamed portal x>".
Thanks so much for any advice you all may have.
-Dan

[Raghavendra]

Post by Dan Ng
Hello,
I am a newb in Postgres coming from Oracle. Does anyone know if it's
possible to see the plan being executed by Postgres for an active sql
statement (from pg_stat_activity). This is to be able to troubleshoot poor
performing ones and in Oracle we are able to retrieve such a plan.
Additionally, how do I see the actual sql statement that shows as "<unnamed portal x>".
Thanks so much for any advice you all may have.
-Dan

Welcome...!!!

You need to use additional module to write query plan in logs i.e.,
AUTO_EXPLAIN.

http://www.postgresql.org/docs/9.2/static/auto-explain.html

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

[Michael Holt]

Of course you can also see what the query plan will be without having to run the query through a standard explain query:

http://www.postgresql.org/docs/9.0/static/sql-explain.html
________________________________
From: pgsql-admin-***@postgresql.org [pgsql-admin-***@postgresql.org] on behalf of Raghavendra [***@enterprisedb.com]
Sent: Wednesday, January 23, 2013 9:52 PM
To: Dan Ng
Cc: pgsql-***@postgresql.org
Subject: Re: [ADMIN] Getting

On Wed, Jan 23, 2013 at 11:50 PM, Dan Ng <***@yahoo.com<mailto:***@yahoo.com>> wrote:
Hello,
I am a newb in Postgres coming from Oracle. Does anyone know if it's possible to see the plan being executed by Postgres for an active sql statement (from pg_stat_activity). This is to be able to troubleshoot poor performing ones and in Oracle we are able to retrieve such a plan.
Additionally, how do I see the actual sql statement that shows as "<unnamed portal x>".
Thanks so much for any advice you all may have.
-Dan

Welcome...!!!

You need to use additional module to write query plan in logs i.e., AUTO_EXPLAIN.

http://www.postgresql.org/docs/9.2/static/auto-explain.html

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

[Greg Williamson]

Dan --

Post by Michael Holt
________________________________
Sent: Wednesday, January 23, 2013 9:55 PM
Subject: Re: [ADMIN] Getting
http://www.postgresql.org/docs/9.0/static/sql-explain.html

I am not familiar with the Oracle tools, but you should note that the "EXPLAIN ANALYZE" does actually do the transaction, so if you doing an update / insert / delete you may want to wrap it in an explicit transaction and then roll it back.

HTH,

Greg Williamson

[Dan Ng]

Hi,
The problem is the current_query (in pg_stat_activity) contains bind variables and I can't just take it to do the explain (and, of course I don't know the values of the bind variables).
 
On a related issue, how can I see the actual sql statement when the current_query shows "<unnamed portal x>".
pg_cursors is only accessible within the same transaction and as a DBA we want to see that cursor of any active transactions.
Thanks,
-Dan



________________________________
From: Greg Williamson <***@yahoo.com>
To:
Cc: "pgsql-***@postgresql.org" <pgsql-***@postgresql.org>
Sent: Thursday, January 24, 2013 1:08 AM
Subject: Re: [ADMIN] Getting

Dan --

Post by Michael Holt
________________________________
Sent: Wednesday, January 23, 2013 9:55 PM
Subject: Re: [ADMIN] Getting
http://www.postgresql.org/docs/9.0/static/sql-explain.html

I am not familiar with the Oracle tools, but you should note that the "EXPLAIN ANALYZE" does actually do the transaction, so if you doing an update / insert / delete you may want to wrap it in an explicit transaction and then roll it back.

HTH,

Greg Williamson