From: Tom Lane <tgl@sss.pgh.pa.us>
To: Viktor Rosenfeld <rosenfel@informatik.hu-berlin.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance of the Materialize operator in a query plan
Date: Mon, 21 Apr 2008 10:44:49 -0400
Message-ID: <11083.1208789089@sss.pgh.pa.us> (raw)
In-Reply-To: <6567C6DA-88EB-4B1D-BEBD-EE15630B044C@informatik.hu-berlin.de>
References: <6567C6DA-88EB-4B1D-BEBD-EE15630B044C@informatik.hu-berlin.de>
Viktor Rosenfeld <rosenfel@informatik.hu-berlin.de> 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
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, rosenfel@informatik.hu-berlin.de
Subject: Re: Performance of the Materialize operator in a query plan
In-Reply-To: <11083.1208789089@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