From: Tom Lane <tgl@sss.pgh.pa.us>
To: joostje@komputilo.org
Cc: pgsql-sql@postgresql.org
Subject: Re: estimates for nested loop very wrong?
Date: Thu, 10 Apr 2003 16:47:52 -0400
Message-ID: <6573.1050007672@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, joostje@komputilo.org
Subject: Re: estimates for nested loop very wrong?
In-Reply-To: <6573.1050007672@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox