I am curious about the information like

  • total optimization time
  • the time spent on each stage of optimization
  • the number of alternative plans and
  • the cost of each alternative plan.
  • 2
    I think you have to modify the source for these... – dezso May 16 '13 at 14:45
  • Yep, as collecting that information would its self have a cost that would slow planning down. – Craig Ringer May 17 '13 at 6:10

You can get some of that information by turning on the configuration parameter log_planner_stats. Most of that information, however, doesn't really exist, because the planner does not fully compute all alternative plans and their costs. It only explores an alternative plan until it can determine that it is slower than the current best plan. So alternative plans are not fully materialized.

| improve this answer | |
  • Thanks for your answer Peter. What I'd like to do is to dive into the optimizer and trace every step the optimizer takes in optimizing a query. Just like the MEMO structure in SQL Server, if you are familiar with SQL Server. I was wondering whether there is something similar to MEMO in PostgreSQL. – zli89 May 20 '13 at 14:32
  • Hi Peter, I have turned on the parameter log_planner_stats, but I do not know where to find the information. In the log files? – zli89 May 28 '13 at 15:22
  • Look for "PLANNER STATISTICS" at level "LOG" in the server log. – Peter Eisentraut May 28 '13 at 18:47
  • By server log, do you mean the log files in the directory data/pg_log? If it is, should I also change the log_statement setting to 'all'? What's weird here is I already did this, but today's log file is empty. – zli89 May 28 '13 at 19:16
  • I have figured out the problem and found the "planner statistics" which is, however, confusing. I'd like to get the optimization time. Can you help me specify, in the following case, which one is the optimization time, if exists? '2013-05-28 15:46:03 EDT LOG: PLANNER STATISTICS 2013-05-28 15:46:03 EDT DETAIL: ! system usage stats: ! 0.000000 elapsed 0.000000 user 0.000000 system sec ! [6.037238 user 1.513209 sys total] – zli89 May 28 '13 at 20:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.