Sometimes, there can be a substantial amount of the rescanning of inner rows required for a Merge Join. And full inner rescans are the expected case when executing a Nested Loop. In this case, as mentioned previously, a Materialize node can be inserted specifically to cache the output from the inner scan, in the hope that will be faster at the expense of using additional memory. This can be particularly valuable on the common inner Index Scan case, where going backwards could potentially even turn into random I/O under particularly unfortunate caching circumstances. Also, not all operators can be rescanned, making materialization necessary in all cases where they're used.
Starting from PostgreSQL ...
