pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedGetting
5+ messages / 4 participants
[nested] [flat]
* Getting
@ 2013-01-23 18:20 Dan Ng <surfnet@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Dan Ng @ 2013-01-23 18:20 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Getting
@ 2013-01-24 05:52 Raghavendra <raghavendra.rao@enterprisedb.com>
parent: Dan Ng <surfnet@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Raghavendra @ 2013-01-24 05:52 UTC (permalink / raw)
To: Dan Ng <surfnet@yahoo.com>; +Cc: pgsql-admin
On Wed, Jan 23, 2013 at 11:50 PM, Dan Ng <surfnet@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/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Getting
@ 2013-01-24 05:55 Michael Holt <MHolt@terapeak.com>
parent: Raghavendra <raghavendra.rao@enterprisedb.com>
0 siblings, 1 reply; 5+ messages in thread
From: Michael Holt @ 2013-01-24 05:55 UTC (permalink / raw)
To: Raghavendra <raghavendra.rao@enterprisedb.com>; Dan Ng <surfnet@yahoo.com>; +Cc: pgsql-admin
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-owner@postgresql.org [pgsql-admin-owner@postgresql.org] on behalf of Raghavendra [raghavendra.rao@enterprisedb.com]
Sent: Wednesday, January 23, 2013 9:52 PM
To: Dan Ng
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Getting
On Wed, Jan 23, 2013 at 11:50 PM, Dan Ng <surfnet@yahoo.com<mailto:surfnet@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/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Getting
@ 2013-01-24 09:08 Greg Williamson <gwilliamson39@yahoo.com>
parent: Michael Holt <MHolt@terapeak.com>
0 siblings, 1 reply; 5+ messages in thread
From: Greg Williamson @ 2013-01-24 09:08 UTC (permalink / raw)
To: ; +Cc: pgsql-admin
Dan --
>________________________________
> From: Michael Holt <MHolt@terapeak.com>
>To: Raghavendra <raghavendra.rao@enterprisedb.com>; Dan Ng <surfnet@yahoo.com>
>Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
>Sent: Wednesday, January 23, 2013 9:55 PM
>Subject: Re: [ADMIN] Getting
>
>
>
>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
>
>
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Getting
@ 2013-01-24 16:57 Dan Ng <surfnet@yahoo.com>
parent: Greg Williamson <gwilliamson39@yahoo.com>
0 siblings, 0 replies; 5+ messages in thread
From: Dan Ng @ 2013-01-24 16:57 UTC (permalink / raw)
To: Greg Williamson <gwilliamson39@yahoo.com>; +Cc: pgsql-admin
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 <gwilliamson39@yahoo.com>
To:
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Thursday, January 24, 2013 1:08 AM
Subject: Re: [ADMIN] Getting
Dan --
>________________________________
> From: Michael Holt <MHolt@terapeak.com>
>To: Raghavendra <raghavendra.rao@enterprisedb.com>; Dan Ng <surfnet@yahoo.com>
>Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
>Sent: Wednesday, January 23, 2013 9:55 PM
>Subject: Re: [ADMIN] Getting
>
>
>
>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
>
>
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2013-01-24 16:57 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-01-23 18:20 Getting Dan Ng <surfnet@yahoo.com>
2013-01-24 05:52 ` Raghavendra <raghavendra.rao@enterprisedb.com>
2013-01-24 05:55 ` Michael Holt <MHolt@terapeak.com>
2013-01-24 09:08 ` Greg Williamson <gwilliamson39@yahoo.com>
2013-01-24 16:57 ` Dan Ng <surfnet@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox