I'm trying to interpret an Explain Analyze, but I did not understand this:

According to the Postgresql documentation at: https://www.postgresql.org/docs/9.6/static/using-explain.html

" the loops value reports the total number of executions of the node, and the actual time and rows values shown are averages per-execution. .... Multiply by the loops value to get the total time actually spent in the node"

But look at this case, in which the total query time was 66 minutes. (Explain Analyze complete and Query at this link: https://sites.google.com/site/eletrolareshop/logo/arqw1-ssd-v3.txt )

What interests me is this section:

  -> Index Scan using idx_l_partkeylineitem000x on lineitem (cost = 0.57..97.65 rows = 26 width = 36)
                  (current time = 23.615..419.113 rows = 30 loops = 26469)
                  Index Cond: (l_partkey = part.p_partkey)

According to the documentation, one should multiply the Actual Time by the number of Loops. That is: 419.113 ms -> 419.113/1000 = 0.41 seconds * 26469 (loops) = 11093.50 seconds or 184.8 minutes.

But how does this stretch take 184.8 minutes, if the entire query ran in 66 minutes?

Of course I'm making a miscalculation, but if anyone can give me a hint as to how I would calculate this time. What I need to know is the time spent go through the idx_l_partkeylineitem000x index, remembering that I did an Explain Analyze which is theoretically the actual time spent and not an estimate as happens with the simple Explain .

thank you and best regards
[] 's Neto

  • 2
    Your query is using parallel execution, that explains why the sum of individual steps is smaller then the overall runtime. Unfortunately the formatting of the plan is completely messed up in the text file, so it's a bit hard to read (or show in explain.depesz.com) – a_horse_with_no_name Sep 8 '17 at 5:54
  • As @a_horse_with_no_name implied: the formatting of the plan, alongside with the indentation is important for reading it correctly. – dezso Sep 8 '17 at 22:04
  • Someone knows some tool that formats explan Plans ? Because when I get them, it comes totally without formatting and really is difficult to visualize. The explain.depesz.com tool does not format if it has broken lines etc. – Neto Sep 8 '17 at 23:19
  • 1
    The plan is returned formatted from the server, if your SQL client doesn't prevent formatting and indention you should try a different SQL client or find a way to preserve formatting in your current client – a_horse_with_no_name Sep 9 '17 at 6:27

Your Answer

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

Browse other questions tagged or ask your own question.