To: joostje@komputilo.org
Cc: pgsql-sql@postgresql.org
Subject: Re: estimates for nested loop very wrong? 
In-reply-to: <20030410194553.GB28537@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>
Comments: In-reply-to joostje@komputilo.org
	message dated "Thu, 10 Apr 2003 21:45:53 +0200"
Date: Thu, 10 Apr 2003 16:23:48 -0400
Message-ID: <6396.1050006228@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

joostje@komputilo.org writes:
> Je 2003/04/10(4)/12:04, Tom Lane skribis:
>> Please try increasing the statistics target (see ALTER TABLE) for db.id, then
>> re-analyze and see if the estimates get better.  The default setting is
>> 10 --- try 20, 50, 100 to see what happens.

> Well, the n_distinct estimates get better, but the cost estimates still
> don't quite add up: `actual cost' is 23.24, cost estimate never gets
> below 49930.

How much RAM do you have on this machine?  If the system is caching
a goodly fraction of the tables, it'd be appropriate to lower
random_page_cost (or increase effective_cache_size).

I do recall a thread awhile back to the effect that the planner
overestimates the cost of nestloop/indexscan plans because it doesn't
account for the fact that successive indexscans aren't independent ---
the top levels of the index btree, at least, are certain to remain
in cache from loop to loop.  That seems unlikely to account for as
large an estimation error as you're showing here, though.  Is there
anything nonrandom about your data statistics?  (For example, could
it be that all the db rows matching a particular tmp0 row are physically
bunched together?)

			regards, tom lane