Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.
share|improve this question
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
add comment

1 Answer

up vote 4 down vote accepted

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.

share|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. –  Zhan Li 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? –  Zhan Li 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. –  Zhan Li 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] –  Zhan Li May 28 '13 at 20:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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