Hi
I encounter an query plan problem like as the following. It's contain two nodes
which assume the result is 1 and 7, but however, the last result is 7418. And
the actual result is just 1, but because of the result is too big, which will
affect the following join methods. And I've analyze the reason, but I think we
can do bettwer.
postgres=# explain select * from test t1 left join test t2 on t1.b = t2.b and
t2.c = 10 where t1.a = 1;
QUERY PLAN
-----------------------------------------------------------------------------
Nested Loop Left Join (cost=0.85..48.16 rows=7418 width=24)
-> Index Scan using a_idx on test t1 (cost=0.43..8.45 rows=1 width=12)
Index Cond: (a = 1)
-> Index Scan using b_idx on test t2 (cost=0.43..39.64 rows=7 width=12)
Index Cond: (b = t1.b)
Filter: (c = 10)
(6 rows)
postgres=# \d test
Table "public.test"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
b | integer | | |
c | integer | | |
Indexes:
"a_idx" btree (a)
"b_idx" btree (b)
Throughing the source code, I know it how to get this result.
Firstly, the result 7 is assume fron the condition t1.b = t2.b and t2.c = 10,
in the function clauselist_selectivity_ext compute the selectivity, and the
result is:
t2.b selc * t2.c = 10 selc * ntuples
(1/134830) * (1002795/1201000) * 1201000 = 7
Secondly, when compute the join selec, the compute function is eqjoinsel,and
the result function is calc_joinrel_size_estimate
case JOIN_LEFT:
nrows = outer_rows * inner_rows * fkselec * jselec;
if (nrows < outer_rows)
nrows = outer_rows;
nrows *= pselec;
break;
outer_rows is 1, inner_rows is 1002795, which is the result the estimate result
of t2.c = 10, while not the 7.
So, through the analyze, I think the reason is the estimate result of
inner_rows, now we just consider the condition t2.c = 10, not the condition
t1.b = t2,b and t2.c = 10.
