Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id AA29F2E0130
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 10 Mar 2008 22:52:19 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 24627-02 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 10 Mar 2008 22:52:17 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from dc.baikal.ru (dc.baikal.ru [212.176.3.237])
	by postgresql.org (Postfix) with ESMTP id B85512E003E
	for <pgsql-performance@postgresql.org>;
	Mon, 10 Mar 2008 22:52:16 -0300 (ADT)
Received: from it319k5.dc-local (it319k5.dc-local [10.0.4.75])
	(authenticated bits=0)
	by dc.baikal.ru (8.13.8/8.13.8) with ESMTP id m2B1qBua022174;
	Tue, 11 Mar 2008 09:52:12 +0800
Message-ID: <47D5E5CC.7020403@dc.baikal.ru>
Date: Tue, 11 Mar 2008 09:52:12 +0800
From: Vlad Arkhipov <arhipov@dc.baikal.ru>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: pgsql-performance@postgresql.org
Subject: Re: Nested loop vs merge join: inconsistencies between
	estimated and actual time
References: <47D0D7B0.7060109@dc.baikal.ru> <7386.1204871746@sss.pgh.pa.us>
In-Reply-To: <7386.1204871746@sss.pgh.pa.us>
Content-Type: multipart/alternative;
	boundary="------------040606000502040903030507"
X-Virus-Scanned: ClamAV version 0.92.1,
	clamav-milter version 0.92.1 on dc.baikal.ru
X-Virus-Status: Clean
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=AWL=-0.001,
	HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200803/217
X-Sequence-Number: 29171

This is a multi-part message in MIME format.
--------------040606000502040903030507
Content-Type: text/plain; charset=windows-1251; format=flowed
Content-Transfer-Encoding: 7bit

Tom Lane writes:
> 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.
>   

I tried to change random_page_cost to 1.1 or something close to it and 
increase/decrease effective_cache_size. But Postgres always prefer plan 
with merge join.

--------------040606000502040903030507
Content-Type: text/html; charset=windows-1251
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=windows-1251"
 http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Tom Lane writes:
<blockquote cite="mid:7386.1204871746@sss.pgh.pa.us" type="cite">
  <pre wrap="">Vlad Arkhipov <a class="moz-txt-link-rfc2396E" href="mailto:arhipov@dc.baikal.ru">&lt;arhipov@dc.baikal.ru&gt;</a> writes:
  </pre>
  <blockquote type="cite">
    <pre wrap="">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...
    </pre>
  </blockquote>
  <pre wrap=""><!---->
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.
  </pre>
</blockquote>
<br>
I tried to change random_page_cost to 1.1 or something close to it and
increase/decrease effective_cache_size. But Postgres always prefer plan
with merge join.
</body>
</html>

--------------040606000502040903030507--