Re: Question about Expected rows value in EXPLAIN output for Nested Loop 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 each rows=10.
> But first of all this is a very exotic join condition (t1.hundred < t2.hundred) and second of all i cannot find any explanation how 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 with a 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 probability 2 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(dot)klaver(at)aklaver(dot)com