Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 223A52E0032 for ; Wed, 27 Feb 2008 11:26:17 -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 99802-02 for ; Wed, 27 Feb 2008 11:26:01 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id B77772E0045 for ; Wed, 27 Feb 2008 11:26:09 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m1RFQ76J028434; Wed, 27 Feb 2008 10:26:07 -0500 (EST) To: "Frits Hoogland" cc: pgsql-performance@postgresql.org Subject: Re: how to identify expensive steps in an explain analyze output In-reply-to: References: Comments: In-reply-to "Frits Hoogland" message dated "Wed, 27 Feb 2008 12:54:01 +0100" Date: Wed, 27 Feb 2008 10:26:07 -0500 Message-ID: <28433.1204125967@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/370 X-Sequence-Number: 28912 "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