Обсуждение: Different row estimations on base rels

Hi,

I noticed the estimated rows of the base relations during the join
searching is *very* different from the estimations in the final plan.

Join search (rows of the initial_rels):
RELOPTINFO (ct): rows=1 width=4
RELOPTINFO (it): rows=1 width=4
RELOPTINFO (mc): rows=17567 width=32
RELOPTINFO (mi_idx): rows=1380035 width=8
RELOPTINFO (t): rows=2528356 width=25

The final plan:
Seq Scan on company_type ct
  (cost=0.00..1.05 rows=1 width=4)
Seq Scan on info_type it
  (cost=0.00..2.41 rows=1 width=4)
Parallel Seq Scan on movie_companies mc
  (cost=0.00..37814.90 rows=7320 width=32)
Parallel Seq Scan on movie_info_idx mi_idx
  (cost=0.00..13685.15 rows=575015 width=8)
Index Scan using title_pkey on title t
  (cost=0.43..0.58 rows=1 width=25)

By looking at the joinrel->rows, I would expect relation t to have
the largest size, however, this is not true at all. I wonder what's
causing this observation, and how to get estimations close to the
final plan?

Thank you,
Donald Dong

On Sun, May 26, 2019 at 1:00 PM Donald Dong <xdong@csumb.edu> wrote:
> I noticed the estimated rows of the base relations during the join
> searching is *very* different from the estimations in the final plan.
>
> Join search (rows of the initial_rels):
> RELOPTINFO (ct): rows=1 width=4
> RELOPTINFO (it): rows=1 width=4
> RELOPTINFO (mc): rows=17567 width=32
> RELOPTINFO (mi_idx): rows=1380035 width=8
> RELOPTINFO (t): rows=2528356 width=25
>
> The final plan:
> Seq Scan on company_type ct
>   (cost=0.00..1.05 rows=1 width=4)
> Seq Scan on info_type it
>   (cost=0.00..2.41 rows=1 width=4)
> Parallel Seq Scan on movie_companies mc
>   (cost=0.00..37814.90 rows=7320 width=32)
> Parallel Seq Scan on movie_info_idx mi_idx
>   (cost=0.00..13685.15 rows=575015 width=8)
> Index Scan using title_pkey on title t
>   (cost=0.43..0.58 rows=1 width=25)
>
> By looking at the joinrel->rows, I would expect relation t to have
> the largest size, however, this is not true at all. I wonder what's
> causing this observation, and how to get estimations close to the
> final plan?

Well, it's all there in the code.  I believe the issue is that the
final estimates are based on the number of rows that will be returned
from the relation, which is often less, and occasionally more, than
the total of the rows in the relation.  The reason it's often less is
because there might be a WHERE clause or similar which rules out some
of the rows.  The reason it might be more is because a nested loop
could return the same rows multiple times.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On May 29, 2019, at 1:36 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> Well, it's all there in the code.  I believe the issue is that the
> final estimates are based on the number of rows that will be returned
> from the relation, which is often less, and occasionally more, than
> the total of the rows in the relation.  The reason it's often less is
> because there might be a WHERE clause or similar which rules out some
> of the rows.  The reason it might be more is because a nested loop
> could return the same rows multiple times.

Yes, indeed. I was confused, and I guess I could've thought about it
about more before posting here. Thank you for answering this
question!

Regards,
Donald Dong