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
