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:23:48 -0400
Message-ID: <6396.1050006228@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:
> 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
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: <6396.1050006228@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