Re: make add_paths_to_append_rel aware of startup cost

On Fri, 16 Feb 2024 at 01:09, David Rowley <dgrowleyml@gmail.com> wrote:
>
> On Thu, 15 Feb 2024 at 21:42, Andy Fan <zhihuifan1213@163.com> wrote:
> > I found the both plans have the same cost, I can't get the accurate
> > cause of this after some hours research, but it is pretty similar with
> > 7516056c584e3, so I uses a similar strategy to stable it. is it
> > acceptable?
>
> It's pretty hard to say.  I can only guess why this test would be
> flapping like this. I see it's happened before on mylodon, so probably
> not a cosmic ray.  It's not like add_path() chooses a random path when
> the costs are the same, so I wondered if something similar is going on
> here that was going on that led to f03a9ca4. In particular, see [1].

While it's not conclusive proof, the following demonstrates that
relpages dropping by just 1 page causes the join order to change.

regression=# explain
regression-# select t1.unique1 from tenk1 t1
regression-# inner join tenk2 t2 on t1.tenthous = t2.tenthous
regression-# union all
regression-# (values(1)) limit 1;
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Limit  (cost=0.00..150.08 rows=1 width=4)
   ->  Append  (cost=0.00..1500965.01 rows=10001 width=4)
         ->  Nested Loop  (cost=0.00..1500915.00 rows=10000 width=4)
               Join Filter: (t1.tenthous = t2.tenthous)
               ->  Seq Scan on tenk1 t1  (cost=0.00..445.00 rows=10000 width=8)
               ->  Materialize  (cost=0.00..495.00 rows=10000 width=4)
                     ->  Seq Scan on tenk2 t2  (cost=0.00..445.00
rows=10000 width=4)
         ->  Result  (cost=0.00..0.01 rows=1 width=4)

regression=# update pg_class set relpages=relpages - 1 where relname = 'tenk2';
UPDATE 1
regression=# explain
regression-# select t1.unique1 from tenk1 t1
regression-# inner join tenk2 t2 on t1.tenthous = t2.tenthous
regression-# union all
regression-# (values(1)) limit 1;
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Limit  (cost=0.00..150.52 rows=1 width=4)
   ->  Append  (cost=0.00..1505315.30 rows=10001 width=4)
         ->  Nested Loop  (cost=0.00..1505265.29 rows=10000 width=4)
               Join Filter: (t1.tenthous = t2.tenthous)
               ->  Seq Scan on tenk2 t2  (cost=0.00..445.29 rows=10029 width=4)
               ->  Materialize  (cost=0.00..495.00 rows=10000 width=8)
                     ->  Seq Scan on tenk1 t1  (cost=0.00..445.00
rows=10000 width=8)
         ->  Result  (cost=0.00..0.01 rows=1 width=4)

I tried this with the proposed changes to the test and the plan did not change.

I've pushed the change now.

David