#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    4
    Rep Power
    0

    Retrieving plan of active sqls


    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. This is to be able to troubleshoot poor performing ones and in Oracle we are able to retrieve such a plan.
    Thanks so much for any advice you all may have.
    -Dan
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,788
    Rep Power
    349
    The only thing I'm aware of that is somewhat similar is the auto_explain module:

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

    But it doesn't store the plan for each statement the way Oracle does it, only for those that exceed a certain threshold. Unlike Oracle PostgreSQL does not (does not need?) to store the plan for every statement.

    You can always get the real execution plan using explain analyze in PostgreSQL (which shows what PostgreSQL is doing rather than what it planned to do).
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    4
    Rep Power
    0
    Hi,
    The problem is I can't do explain analyze as the current_query (shown in pg_stat_activity) contains bind variable(s).
    And, I also don't understand why Postgres would not have a plan. It needs one in order to execute any sql statement, right?
    Thanks!
    -Dan
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,788
    Rep Power
    349
    Originally Posted by surfnetdz
    And, I also don't understand why Postgres would not have a plan. It needs one in order to execute any sql statement, right?
    It has a plan, it just doesn't store it beyond the execution of the statement.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    4
    Rep Power
    0
    Hello again,
    On a related issue, is there a way to see what the actual current_query (as displayed in pg_stat_activity) when it only shows "<unnamed portal 2>".
    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

IMN logo majestic logo threadwatch logo seochat tools logo