SQL explain plan: what is Materialize?

A materialize node means the output of whatever is below it in the tree (which can be a scan, or a full set of joins or something like that) is materalized into memory before the upper node is executed. This is usually done when the outer node needs a source that it can re-scan for some reason or other.

So in your case, the planner is determining that the result of a scan on one of your tables will fit in memory, and it till make it possible to choose an upper join operation that requires rescans while still being cheaper.

It means that it can't use any index (or similar method) to make the join efficient, so as a last resort is materializes the result from one of the tables to have a smaller set to work with when joining against the other table.

In merge join and nested loop join, the database will "rescan" the inner loop.Basically like:

for each row in outer table:    for each row in inner table:        # do something

The planner will materializes the inner loop table which means load the whole table in an in-memory buffer to avoid the expensive disk IO cost.

A useful link.