Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 2F8D92E0047 for ; Mon, 21 Apr 2008 11:45:41 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 83548-01 for ; Mon, 21 Apr 2008 11:45:34 -0300 (ADT) 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 developer.postgresql.org (Postfix) with ESMTP id 3E8582E0074 for ; Mon, 21 Apr 2008 11:45:34 -0300 (ADT) 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 m3LEinpQ011084; Mon, 21 Apr 2008 10:44:50 -0400 (EDT) To: Viktor Rosenfeld cc: pgsql-performance@postgresql.org Subject: Re: Performance of the Materialize operator in a query plan In-reply-to: <6567C6DA-88EB-4B1D-BEBD-EE15630B044C@informatik.hu-berlin.de> References: <6567C6DA-88EB-4B1D-BEBD-EE15630B044C@informatik.hu-berlin.de> Comments: In-reply-to Viktor Rosenfeld message dated "Mon, 21 Apr 2008 13:07:22 +0200" Date: Mon, 21 Apr 2008 10:44:49 -0400 Message-ID: <11083.1208789089@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200804/301 X-Sequence-Number: 29794 Viktor Rosenfeld writes: > I'm having trouble understanding the cost of the Materialize > operator. Consider the following plan: > Nested Loop (cost=2783.91..33217.37 rows=78634 width=44) (actual > time=77.164..2478.973 rows=309 loops=1) > Join Filter: ((rank2.pre <= rank5.pre) AND (rank5.pre <= > rank2.post)) > -> Nested Loop (cost=0.00..12752.06 rows=1786 width=33) > (actual time=0.392..249.255 rows=9250 loops=1) > ..... > -> Materialize (cost=2783.91..2787.87 rows=396 width=22) > (actual time=0.001..0.072 rows=587 loops=9250) > -> Nested Loop (cost=730.78..2783.51 rows=396 > width=22) (actual time=7.637..27.030 rows=587 loops=1) > .... > The cost of the inner-most Nested Loop is 27 ms, but the total cost of > the Materialize operator is 666 ms (9250 loops * 0.072 ms per > iteration). So, Materialize introduces more than 10x overhead. Not hardly. Had the Materialize not been there, we'd have executed the inner nestloop 9250 times, for a total cost of 9250 * 27ms. (Actually it might have been less due to cache effects, but still a whole lot more than 0.072 per iteration.) These numbers say that it's taking the Materialize about 120 microsec per row returned, which seems a bit high to me considering that the data is just sitting in a tuplestore. I surmise that you are using a machine with slow gettimeofday() and that's causing the measurement overhead to be high. regards, tom lane