PostgreSQL: The actual rows number of Materialized Merge Join Node does not inherit the inner node

Question

I found a plan to perform strangely. The actual rows number of Materialized Merge Join Node does not inherit the inner node, but is consistent with the parent node. The visualization of the plan is shown in the figure:.

Or, the plan looks like this.

Merge Join  (cost=1978033.13..6438858.71 rows=10000000000 width=264) (actual time=12293.287..7386991.093 rows=9537310863 loops=1)
  Buffers: shared hit=12754119 read=565409, temp read=233593434 written=2319054
  ->  Index Scan using mk_movie_id_btree_index on movie_keyword mk  (cost=0.43..112657.18 rows=4523930 width=12) (actual time=0.032..1779.290 rows=4523912 loops=1)
        Buffers: shared hit=21582 read=29540
  ->  Materialize  (cost=1977981.67..3883029.20 rows=100000000 width=252) (actual time=12293.130..1922032.085 rows=9543101849 loops=1)
        Buffers: shared hit=12732537 read=535869, temp read=233593434 written=2319054
        ->  Merge Join  (cost=1977981.67..3633029.20 rows=100000000 width=252) (actual time=12293.126..115733.244 rows=90649862 loops=1)
              Buffers: shared hit=12732537 read=535869, temp read=89233 written=89436
              ->  Index Scan using mc_movie_id_btree_index on movie_companies mc  (cost=0.43..123259.31 rows=2609129 width=40) (actual time=0.021..1521.962 rows=2609129 loops=1)
                    Buffers: shared hit=1213119 read=24071
              ->  Materialize  (cost=1977947.84..3374788.05 rows=10000000 width=212) (actual time=12292.454..61205.574 rows=93231469 loops=1)
                    Buffers: shared hit=11519418 read=511798, temp read=89233 written=89436
                    ->  Merge Join  (cost=1977947.84..3349788.05 rows=10000000 width=212) (actual time=12292.451..44614.188 rows=9273754 loops=1)
                          Buffers: shared hit=11519418 read=511798, temp read=89233 written=89436
                          ->  Index Scan using mi_movie_id_btree_index on movie_info mi  (cost=0.43..974619.15 rows=2969419 width=76) (actual time=0.024..10182.451 rows=3036482 loops=1)
                                  Filter: (mi.info_type_id = 16)
                                  Rows Removed by Filter: 11797983
                                Buffers: shared hit=9030642 read=214814
                          ->  Materialize  (cost=1977938.97..2216782.67 rows=10000000 width=136) (actual time=12292.351..28321.485 rows=9438941 loops=1)
                                Buffers: shared hit=2488776 read=296984, temp read=89233 written=89436
                                ->  Merge Join  (cost=1977938.97..2191782.67 rows=10000000 width=136) (actual time=12292.347..23593.269 rows=4402209 loops=1)
                                      Buffers: shared hit=2488776 read=296984, temp read=89233 written=89436
                                      ->  Index Scan using title_pkey on title t  (cost=0.43..116661.50 rows=1376950 width=94) (actual time=0.024..3475.464 rows=1381090 loops=1)
                                              Filter: (t.production_year > 2000)
                                              Rows Removed by Filter: 1144656
                                            Buffers: shared hit=2488057 read=45016
                                      ->  Materialize  (cost=1977925.80..2014176.19 rows=7250077 width=42) (actual time=12292.290..16380.797 rows=7451702 loops=1)
                                            Buffers: shared hit=719 read=251968, temp read=89233 written=89436
                                            ->  Sort  (cost=1977925.80..1996050.99 rows=7250077 width=42) (actual time=12292.286..14197.068 rows=7451702 loops=1)
                                                    Sort Key: ci.movie_id
                                                    Sort Method: external merge Disk: 247232kB
                                                  Buffers: shared hit=719 read=251968, temp read=89233 written=89436
                                                  ->  Seq Scan on cast_info ci  (cost=0.00..705741.30 rows=7250077 width=42) (actual time=2179.382..6734.497 rows=7451973 loops=1)
                                                          Filter: (ci.role_id = 2)
                                                          Rows Removed by Filter: 28792371
                                                        Buffers: shared hit=719 read=251968
Planning:
  Buffers: shared hit=256
Execution time: 7878180.992 ms

Answer 1

In a merge join, if there is a sequence of equal values of the join key in the first child, then the relevant segment of the second child needs to be rescanned multiple times, and rows are counted in the Materialize each time they are rescanned. But for the node below the Materialize, each row is only scanned once, as the Materialize buffers them.