Re: Question about Expected rows value in EXPLAIN output for NestedLoop node

On 5/11/19 2:33 AM, bb ddd wrote:
> 
> Thanks, Adrian, but i was looking for something that goes into more depth.
> For example there is one case described there where we have a Nested Loop with rows=33, and its 2 child nodes have
eachrows=10.
 
> But first of all this is a very exotic join condition (t1.hundred < t2.hundred) and second of all i cannot find any
explanationhow this number 33 is derived. They literally spend less than 2 sentences on this case.
 
> 
> In my case i as well have Nested Loop's rows value different than the product of its 2 children's rows values, but
witha normal join condition on a foreign key.
 
> My guesses (again) are the same way it keeps some statistics (very curious what exactly) about what is the
probability2 random rows from each table satisfy that condition 
 

https://www.postgresql.org/docs/11/planner-stats-details.html

(t1.hundred < t2.hundred), it also keeps statistics what is the 
probability 2 random rows from each table satisfy the regular normal 
join condition like the one i have in my case (basically 
t1.t2_id=t2.id). And in both cases it just applies that probability to 
the product of the rows values of the 2 child nodes, to calculate the 
expected rows value of the result of the Nested Loop. But i am just 
guessing.
> 
> 
> 
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com