Re: Performance of the Materialize operator in a query plan - Mailing list pgsql-performance

From Viktor Rosenfeld
Subject Re: Performance of the Materialize operator in a query plan
Date
Msg-id 316F5B37-3D07-417B-B5C1-9DB973258B18@informatik.hu-berlin.de
Whole thread Raw
In response to Re: Performance of the Materialize operator in a query plan  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Performance of the Materialize operator in a query plan  (PFC <lists@peufeu.com>)
List pgsql-performance
Hi Tom,

>> 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.)

I realize that Materialize saves a big amount of time in the grand
scheme, but I'm still wondering about the descrepancy between the
total cost of Materialize and the contained Nested Loop.

> 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.

Do you mean, that the overhead is an artefact of timing the query?  In
that case, the query should run faster than its evaluation with
EXPLAIN ANALYZE, correct?

Is there a way to test this assumption regarding the speed of
gettimeofday?  I'm on a Macbook and have no idea about the performance
of its implementation.

Cheers,
Viktor

pgsql-performance by date:

Previous
From: "Nikolas Everett"
Date:
Subject: Question about disk IO an index use and seeking advice
Next
From: Matthew Wakeling
Date:
Subject: Re: Question about disk IO an index use and seeking advice