On Fri, 16 Feb 2024 at 01:09, David Rowley <dgrowle...@gmail.com> wrote:
>
> On Thu, 15 Feb 2024 at 21:42, Andy Fan <zhihuifan1...@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
