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 10:17:09 -0400
Message-ID: <3742.1049984229@sss.pgh.pa.us> (raw)
In-Reply-To: <20030410112144.GA22716@co.uea.org>
References: <20030410112144.GA22716@co.uea.org>
joostje@komputilo.org writes:
> When JOINing two tabels (one with 23 entries, one with 2.5e6 entries),
> psql estimates the cost of the nested loop method way to high, causing
> it to use Hash Join, even though Hash Join actually takes 30 seconds,
> agianst 0.020 seconds for Nested Loop.
Have you done an ANALYZE or VACUUM ANALYZE recently?
> Nested Loop (cost=0.00..208256.60 rows=61140 width=38) (actual time=0.92..18.49 rows=756 loops=1)
> -> Seq Scan on tmp1 (cost=0.00..1.23 rows=23 width=7) (actual time=0.24..0.39 rows=23 loops=1)
> -> Index Scan using db_id_idx on db (cost=0.00..9021.35 rows=2658 width=31) (actual time=0.32..0.69 rows=33 loops=23)
> Total runtime: 19.20 msec
The planner is evidently estimating that each row of tmp1 will match 2600+
rows of db, whereas in reality there is only one match. Rather than
mess with enable_hashjoin, you need to find out why that estimate is so
badly off. Are the entries in tmp1 specially selected to correspond to
unique rows of db?
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: <3742.1049984229@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