From: Tom Lane <tgl@sss.pgh.pa.us>
To: Vlad Arkhipov <arhipov@dc.baikal.ru>
Cc: pgsql-performance@postgresql.org
Subject: Re: Nested loop vs merge join: inconsistencies between estimated and actual time
Date: Fri, 07 Mar 2008 01:35:46 -0500
Message-ID: <7386.1204871746@sss.pgh.pa.us> (raw)
In-Reply-To: <47D0D7B0.7060109@dc.baikal.ru>
References: <47D0D7B0.7060109@dc.baikal.ru>
Vlad Arkhipov <arhipov@dc.baikal.ru> writes:
> I've came across this issue while writing report-like query for 2 not
> very large tables. I've tried several methods to resolve this one (see
> below). But now I'm really stuck...
It looks like you are wishing to optimize for all-in-memory situations,
in which case the traditional advice is to reduce random_page_cost to
something close to 1. AFAICS all the rowcount estimates you're seeing
are spot on, or as close to spot on as you could realistically hope for,
and so the problem lies with the cost parameters. Fooling with the
statistics is not going to help if the rowcount estimates are already
good.
(Note: the apparent undercounts you're seeing on indexscans on the outer
side of a mergejoin seem to be because the mergejoin terminates early
due to limited range of the other input join key. The planner is
expecting this, as we can see because the predicted cost of the join is
actually much less than the predicted cost of running the input
indexscan to completion. The cost ratio is about consistent with the
rowcount ratio, which makes me think it got these right too.)
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-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, arhipov@dc.baikal.ru
Subject: Re: Nested loop vs merge join: inconsistencies between estimated and actual time
In-Reply-To: <7386.1204871746@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