Re: query plan - Mailing list pgsql-general

From Tom Lane
Subject Re: query plan
Date
Msg-id 757726.1700252046@sss.pgh.pa.us
Whole thread Raw
In response to query plan  (Torsten Förtsch <tfoertsch123@gmail.com>)
List pgsql-general
=?UTF-8?Q?Torsten_F=C3=B6rtsch?= <tfoertsch123@gmail.com> writes:
> This is part of a query plan:

>  Nested Loop Left Join  (cost=26.32..47078866.36 rows=1344945195 width=626)
>    ->  Nested Loop Left Join  (cost=25.74..5312.48 rows=1344945195
> width=608)
>          ->  Nested Loop Left Join  (cost=6.79..2876.77 rows=102 width=373)
>                ->  Nested Loop Left Join  (cost=1.90..1965.51 rows=102
> width=361)
>                ->  Bitmap Heap Scan on ...  (cost=4.89..8.91 rows=2
> width=28)
>          ->  Hash Left Join  (cost=18.95..42.61 rows=3 width=243)
>                ->  Hash Left Join  (cost=18.94..42.59 rows=3 width=203)
>                ->  Hash  (cost=0.00..0.00 rows=1 width=48)
>    ->  Memoize  (cost=0.58..4.59 rows=1 width=172)

> What I don't understand is this. The left node of the join is expected to
> return 102 rows. The right node 3. How can this result in >1e9 rows?

The rowcount estimate for the join is not derived by multiplying the
rowcount estimates of the two inputs.  Having said that, this looks
pretty inconsistent.  Can you show a test case that acts like that?

            regards, tom lane



pgsql-general by date:

Previous
From: Torsten Förtsch
Date:
Subject: query plan
Next
From: Anthony Apollis
Date:
Subject: Getting most records possibly from an Inner/Left Join