From: Tom Lane <tgl@sss.pgh.pa.us>
To: Vincent van Leeuwen <pgsql.spam@vinz.nl>
Cc: pgsql-performance@postgresql.org
Subject: Re: tweaking costs to favor nestloop
Date: Wed, 11 Jun 2003 16:17:53 -0400
Message-ID: <23557.1055362673@sss.pgh.pa.us> (raw)
In-Reply-To: <20030611193313.GE1535@md2.mediadesign.nl>
References: <20030611193313.GE1535@md2.mediadesign.nl>
Vincent van Leeuwen <pgsql.spam@vinz.nl> writes:
> I'm unable to tweak the various _cost settings in such a way that attached
> query will use the right plan.
You aren't going to be able to. You've already overshot a reasonable
random_page_cost setting --- to judge by the relative actual costs of
the merge and hash join, a value somewhere around 3 is appropriate for
your setup. (Assuming I did the math right --- if you set it to 3,
do you get a ratio of merge and hash estimated costs that agrees with
the ratio of actual runtimes?)
The problem here is that the costing of the repeated inner index scans
isn't realistic: 35417 probes into "auth" are clearly taking much less
than 35417 times what a single probe could be expected to take. We
talked about how repeated scans would win from caching of the upper
btree levels, but I think there's more to it than that. It occurs to me
that the probes you are making are probably not random and uncorrelated.
They are driven by the values of reportuser.idreporter ... is it fair
to guess that most of the reportuser rows link to just a small fraction
of the total auth population? If so, the caching could be eliminating
most of the reads, not just the upper btree levels, because we're
mostly hitting only small parts of the index and auth tables.
I'm beginning to think that the only reasonable way to model this is to
cost the entire nestloop join as a unit, so that we have access to
statistics about the outer table as well as the indexed table. That
would give us a shot at estimating how much of the index is likely to
get touched.
As of 7.3 I think all you can do is force nestloop by disabling the
other two join types.
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, pgsql.spam@vinz.nl
Subject: Re: tweaking costs to favor nestloop
In-Reply-To: <23557.1055362673@sss.pgh.pa.us>
References: <20030611193313.GE1535@md2.mediadesign.nl> <23557.1055362673@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