Re: estimates for nested loop very wrong?

joostje@komputilo.org writes:
> For example, for the stat.targ=500 run:
> => explain analyse SELECT  id  from db, tmp0 WHERE valida AND poseda='uea'  AND tab='pers' AND tmp0.v0=id ;
> NOTICE:  QUERY PLAN:

> Nested Loop  (cost=0.00..121000.31 rows=28184 width=39) (actual time=1.05..23.24 rows=415 loops=1)
>   ->  Seq Scan on tmp0  (cost=0.00..20.00 rows=1000 width=32) (actual time=0.22..0.40 rows=29 loops=1)
                          ^^^^^^^^^
 
>   ->  Index Scan using db_id_idx on db  (cost=0.00..120.63 rows=28 width=7) (actual time=0.27..0.75 rows=14
loops=29)
> Total runtime: 23.92 msec

Actually, I see part of the problem: you haven't vacuumed
tmp0, so it's sitting at the default size estimate of 1000 rows.
That accounts for more than a factor of 30 in the estimation error
in the nestloop plan, while it wouldn't have nearly as much impact
on hash or mergejoin estimates.

There's still a good big error left to account for though :-(

I don't think you mentioned the other WHERE conditions before.  Which
table are those restricting, and how selective are they?
        regards, tom lane