Re: estimates for nested loop very wrong?

joostje(at)komputilo(dot)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