SQL explain plan: what is Materialize?

Question

I asked PostgreSQL to explain my query. Part of the explanation was:

table_name --> Materialize 

What does materialize do? I'm joining two tables, not views or anything like that.

Answer 1

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.

Answer 2

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.