From: Tom Lane <tgl@sss.pgh.pa.us>
To: Frits Hoogland <frits.hoogland@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: how to identify expensive steps in an explain analyze output
Date: Wed, 27 Feb 2008 10:26:07 -0500
Message-ID: <28433.1204125967@sss.pgh.pa.us> (raw)
In-Reply-To: <fbb8fbcd0802270354g3b80e90bgfeaa3bf6536cabb3@mail.gmail.com>
References: <fbb8fbcd0802270218u3d27c829y5f615250e9786a61@mail.gmail.com>
<fbb8fbcd0802270354g3b80e90bgfeaa3bf6536cabb3@mail.gmail.com>
"Frits Hoogland" <frits.hoogland@gmail.com> writes:
> The manual states: "Actually two numbers are shown: the start-up time before
> the first row can be returned, and the total time to return all the rows.".
> Does this mean that the difference between the first and second is the cost
> or the time the step in the explain has taken?
No, or at least only for a very strange definition of "cost". An
example of the way these are used is that for a hash join, the startup
time would include the time needed to scan the inner relation and build
the hash table from it. The run time (ie, difference between startup
and total) represents the part of the process where we're scanning the
outer relation and probing into the hash table for matches. Rows are
returned as matches are found during this part of the process. I can't
think of any useful definition under which the startup time would be
ignored.
The reason the planner divides the total cost like this is that in the
presence of LIMIT or a few other SQL features, it may not be necessary
to run the plan to completion, but only to fetch the first few rows.
In this case a plan with low startup cost may be preferred, even though
the estimated total cost to run it to completion might be higher than
some other plan has. We're not *going* to run it to completion, and
so the really interesting figure is startup cost plus some appropriate
fraction of run cost. You can see this at work if you look at the
EXPLAIN numbers for a query involving a LIMIT.
The whole thing might make a bit more sense if you read
http://www.postgresql.org/docs/8.3/static/overview.html
particularly the last two subsections.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, frits.hoogland@gmail.com
Subject: Re: how to identify expensive steps in an explain analyze output
In-Reply-To: <28433.1204125967@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox