Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 6EA682E00A4
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Fri,  7 Mar 2008 02:35:58 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 58128-07 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Fri,  7 Mar 2008 02:35:49 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id 9BEC02E0099
	for <pgsql-performance@postgresql.org>;
	Fri,  7 Mar 2008 02:35:51 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m276ZkjY007387;
	Fri, 7 Mar 2008 01:35:47 -0500 (EST)
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
In-reply-to: <47D0D7B0.7060109@dc.baikal.ru> 
References: <47D0D7B0.7060109@dc.baikal.ru>
Comments: In-reply-to Vlad Arkhipov <arhipov@dc.baikal.ru>
	message dated "Fri, 07 Mar 2008 13:50:40 +0800"
Date: Fri, 07 Mar 2008 01:35:46 -0500
Message-ID: <7386.1204871746@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200803/148
X-Sequence-Number: 29102

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