Re: Materializing the relation

>> QUERY PLAN
>> ----------------------------------------------------------------
>> Nested Loop (cost=1.03..2.34 rows=3 width=24)
>> Join Filter: (tb1.c1 = tb2.c1)
>> -> Seq Scan on tb2 (cost=0.00..1.04 rows=4 width=12)
>> -> Materialize (cost=1.03..1.06 rows=3 width=12)
>> -> Seq Scan on tb1 (cost= 0.00..1.03 rows=3 width=12)
>>
>>
>> In this above plan, what does 'Material' mean?

It means it will read it all in to memory (or disk if it runs out of memory)
and use that instead of referring to the original table subsequently. In this
case it will only make a small difference in performance because the temporary
storage will be very similar to the original table. It just allows Postgres to
pack the data a bit more densely and skip MVCC visibility checks.

Other times Materialize nodes are used where they make a bigger difference are
when the plan beneath it is quite expensive and we don't want to have to
re-execute it more than necessary.

"Shoaib Mir" <shoaibmir(at)gmail(dot)com> writes:

> From /src/include/utils/tuplestore.h
>
> "The materialize shields the sort from the need to do mark/restore and
> thereby allows it to perform its final merge pass on-the-fly; while the
> materialize itself is normally cheap since it won't spill to disk unless the
> number of tuples with equal key values exceeds work_mem"

This comment actually refers to a new optimization which isn't in the released
8.2 yet. It introduces a Materialize node above a sort to allow the sort to
skip the final merge step. Instead it merges as the query runs and the
Materialize throws away data which isn't needed any more since merge joins
only need the current key value and they don't need to refer back to previous
key values.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com