Skip site navigation (1) Skip section navigation (2)

Performance of the Materialize operator in a query plan

From: Viktor Rosenfeld <rosenfel(at)informatik(dot)hu-berlin(dot)de>
To: pgsql-performance(at)postgresql(dot)org
Subject: Performance of the Materialize operator in a query plan
Date: 2008-04-21 11:07:22
Message-ID: 6567C6DA-88EB-4B1D-BEBD-EE15630B044C@informatik.hu-berlin.de (view raw or flat)
Thread:
Lists: pgsql-performance
Hi,

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.  Is  
this the cost of writing the table to temporary storage or am I  
misreading the query plan output?

Furthermore, the outer table is almost 20x as big as the inner table.   
Wouldn't the query be much faster by switching the inner with the  
outer table?  I have switched off GEQO, so I Postgres should find the  
optimal query plan.

Cheers,
Viktor

Responses

pgsql-performance by date

Next:From: Erik JonesDate: 2008-04-21 14:04:28
Subject: Re: connections slowing everything down?
Previous:From: Adrian MoiseyDate: 2008-04-21 09:50:31
Subject: connections slowing everything down?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group