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

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 (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

>Относно: Re: Question about Expected rows value in EXPLAIN output for Nested
Loop node

>До: bb ddd <nnickoloff1234(at)abv(dot)bg>

>Изпратено на: 10.05.2019 19:02

> On 5/10/19 8:56 AM, bb ddd wrote:

> > Thanks for the reply. Here it is: https://explain.depesz.com/s/LQCS

> >

> > My main trouble is not with this exact case, but i am looking for a general description of the algorithm how those numbers are calculated. In the meanwhile i also constructed a couple of artificial tables to experiment with and see how these numbers change, and am pretty sure my guesses there in the original question are quite close to what is going on, but would be so much easier to read explain plans, if there was some documentation about how the expected rows are calculated for different nodes, in different contexts (like when loops=1 vs loops>1) maybe what statistics they use, how they depend on the numbers of their child nodes etc.

> >

> >

>

> How about?:

> https://www.postgresql.org/docs/11/using-explain.html

>

>

> --

> Adrian Klaver

> adrian(dot)klaver(at)aklaver(dot)com