Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 2FB592E0032 for ; Wed, 27 Feb 2008 13:36:59 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 71878-04 for ; Wed, 27 Feb 2008 13:36:53 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from rv-out-0910.google.com (rv-out-0910.google.com [209.85.198.190]) by postgresql.org (Postfix) with ESMTP id 7F4D42E003D for ; Wed, 27 Feb 2008 13:36:53 -0400 (AST) Received: by rv-out-0910.google.com with SMTP id g13so2066882rvb.43 for ; Wed, 27 Feb 2008 09:36:52 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=br9PT5EouMFAcBNJxfW9tpaJuyKMScdls35gLxonBbQ=; b=njdNrrLeeQ99MzSvPPyKA+1Of+19qpMc59BgRbolcuLs15EKj41b91EwjiECQKBVyMRA889uKkQXQ0trlhORywnPBYOHiMcK5nxoU4Px55dHLlQ8EpX4VL+sLgBdkUarosCJltO/iXHluRWTmHb26o8cL45u99tQAvzwGezGLng= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=UF/oIz2RcCIwhWhoeOKi1IPn+MNZj0GNR183rh0+miSLmlnDhJIbQTIllxBA1vsd7OIZhZPYhQNqKUkLDI6Lqeg7NzrtfyexXCbfETJmceOdj4kIsjMbogBr6AIOFi1hXKpbiDoqVuHZSGiDt305C0kweInLaT1h2KtGhCxinvI= Received: by 10.141.99.4 with SMTP id b4mr1525771rvm.196.1204133812609; Wed, 27 Feb 2008 09:36:52 -0800 (PST) Received: by 10.64.195.18 with HTTP; Wed, 27 Feb 2008 09:36:52 -0800 (PST) Message-ID: Date: Wed, 27 Feb 2008 18:36:52 +0100 From: "Frits Hoogland" To: "Tom Lane" Subject: Re: how to identify expensive steps in an explain analyze output Cc: pgsql-performance@postgresql.org In-Reply-To: <28433.1204125967@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_16218_29809411.1204133812577" References: <28433.1204125967@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200802/372 X-Sequence-Number: 28914 ------=_Part_16218_29809411.1204133812577 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline thanks for your answer! okay, cost is a totally wrong word here. I am using the "actual times" in the execplan. what I am trying to do, is optimise a database both by investigating execplans, and thinking about what concurrency would do to my database. (I have a database which is reported to almost stop functioning under load) I've read the sections you pointed out. It's quite understandable. What I am missing, is the connection between the logical steps (ie. the execplan) and the physical implications. For example: the time it took for a seqscan to complete can be computed by subtracting the two times after "actual time" (because it's an end node in the execplan, is that assumption (scantime=totaltime-startuptime) right?) I can compute how long it approximately took for each row by dividing the time through the number of rows (and loops) but I do not know how many physical IO's it has done, and/or how many logical IO's Same for merge joins&sorts: the physical implication (writing and reading if the amount of data exceeds work_mem) is not in the execplan. that's the reason I mentioned "cost". I know understand that it's impossible to judge the "cost" of a merge join, because it's time is composited by both the scans and the merge operation itself, right? Is there any way to identify nodes in the execplan which "cost" many (CPU time, IO, etc.)? regards frits On 2/27/08, Tom Lane wrote: > > "Frits Hoogland" 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 > ------=_Part_16218_29809411.1204133812577 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline thanks for your answer!

okay, cost is a totally wrong word here. I am using the "actual times" in the execplan.

what I am trying to do, is optimise a database both by investigating execplans, and thinking about what concurrency would do to my database. (I have a database which is reported to almost stop functioning under load)

I've read the sections you pointed out. It's quite understandable.

What I am missing, is the connection between the logical steps (ie. the execplan) and the physical implications. 
For example: the time it took for a seqscan to complete can be computed by subtracting the two times after "actual time" 
(because it's an end node in the execplan, is that assumption (scantime=totaltime-startuptime) right?)
I can compute how long it approximately took for each row by dividing the time through the number of rows (and loops)
but I do not know how many physical IO's it has done, and/or how many logical IO's
Same for merge joins&sorts: the physical implication (writing and reading if the amount of data exceeds work_mem) is not in the execplan. that's the reason I mentioned "cost".
I know understand that it's impossible to judge the "cost" of a merge join, because it's time is composited by both the scans and the merge operation itself, right?

Is there any way to identify nodes in the execplan which "cost" many (CPU time, IO, etc.)?

regards

frits

On 2/27/08, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"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

------=_Part_16218_29809411.1204133812577--