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:47:52 -0400
Message-ID: <6573.1050007672@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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