Date: Fri, 11 Apr 2003 00:20:56 +0200
From: joostje@komputilo.org
To: pgsql-sql@postgresql.org
Cc: joostje@komputilo.org
Subject: Re: estimates for nested loop very wrong?
Message-ID: <20030410222056.GA4359@co.uea.org>
References: <20030410112144.GA22716@co.uea.org>
 <3742.1049984229@sss.pgh.pa.us>
	<20030410162902.GA28537@co.uea.org> <4867.1049993075@sss.pgh.pa.us>
	<20030410194553.GB28537@co.uea.org> <6573.1050007672@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <6573.1050007672@sss.pgh.pa.us>
User-Agent: Mutt/1.4i

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

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