Postgres Pro
Downloads
Home   >   mailing lists

Thread: Meaning of "loops" in EXPLAIN ANALYSE output

Meaning of "loops" in EXPLAIN ANALYSE output

From
Aaron Bingham
Date:
Hi,

I have a query optimization problem and I have failed to find the part
of the Postgres docs that explains the meaning of the "loops" value in
the EXPLAIN ANALYSE output.

For example, my EXPLAIN ANALYSE output contains the following line:

Unique  (cost=9775.21..10015.32 rows=1 width=8) (actual
time=264.889..264.889 rows=1 loops=791)

Does that mean that the entire operation took 264.889 ms, or that a
single iteration took that long?  The time for the entire query would
suggest the latter interpretation but I'd like to rule out the
possibility that something else is causing the execution time to balloon.

Thanks,

--
--------------------------------------------------------------------
Aaron Bingham
Senior Software Engineer
Cenix BioScience GmbH
--------------------------------------------------------------------

Re: Meaning of "loops" in EXPLAIN ANALYSE output

From
Richard Huxton
Date:
Aaron Bingham wrote:
> Unique  (cost=9775.21..10015.32 rows=1 width=8) (actual
> time=264.889..264.889 rows=1 loops=791)
>
> Does that mean that the entire operation took 264.889 ms, or that a
> single iteration took that long?  The time for the entire query would
> suggest the latter interpretation

You're quite right - multiply the time by the number of loop iterations
to find the total.

--
   Richard Huxton
   Archonet Ltd

Re: Meaning of "loops" in EXPLAIN ANALYSE output

From
Martijn van Oosterhout
Date:
On Mon, Apr 10, 2006 at 04:08:01PM +0100, Richard Huxton wrote:
> Aaron Bingham wrote:
> >Unique  (cost=9775.21..10015.32 rows=1 width=8) (actual
> >time=264.889..264.889 rows=1 loops=791)
> >
> >Does that mean that the entire operation took 264.889 ms, or that a
> >single iteration took that long?  The time for the entire query would
> >suggest the latter interpretation
>
> You're quite right - multiply the time by the number of loop iterations
> to find the total.

Or rather, the time given is the total time divided by the number of
loops i.e. the average. It's done this way because the planning
estimate is also done on those terms (average per iteration not grand
total).

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
Attachment

Re: Meaning of "loops" in EXPLAIN ANALYSE output

From
Tom Lane
Date:
Aaron Bingham <bingham@cenix-bioscience.com> writes:
> For example, my EXPLAIN ANALYSE output contains the following line:

> Unique  (cost=9775.21..10015.32 rows=1 width=8) (actual
> time=264.889..264.889 rows=1 loops=791)

> Does that mean that the entire operation took 264.889 ms, or that a
> single iteration took that long?

That's the average time per full execution cycle of the node, ie, we
actually spent 264.889 * 791 msec here or in its subnodes.  Presumably
this node is in a subquery, or on the inside of a nestloop, or something
else that would demand its output more than once.

            regards, tom lane