Re: estimates for nested loop very wrong?

Je 2003/04/10(4)/16:04, Tom Lane skribis:
> 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 :-(

OK, here it goes:

db.id.statistics target set to 10 (default);
effective_cache_size=1000 (default);
random_page_cost=4 (default);
=> estimated cost = 3690777.90

analyse tmp0;
=> estimated cost = 107033.27

set effective_cache_size=100000;
=> estimated cost = 107033.27 (no change there)

set random_page_cost=1; (default was 4)
=> estimated cost = 27592.06

db.id.statistics target = 500; (improves n_distinct estimate from 2041 to 39909)
=> estimated cost = 943.44

set enable_nestloop = off;
=> planner elects Hash Loop, estimated/real cost: 52834.33 12762.86

set enable_hashjoin = off;
=> planner elects Merge Join, estimated/real cost: 179961.75/54314.09

So, it seems everything is working as it should now!
Thanks!

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

Ah, sorry, they are worth not mentioning, as db.poseda='uea' and db.tab='pers' and valida
for about 97.6% of the rows. I guess I should have done the tests without
the two conditions, as they have no influence.

Thanks!
joostje