From: Vlad Arkhipov <arhipov@dc.baikal.ru>
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
Date: Tue, 11 Mar 2008 09:52:12 +0800
Message-ID: <47D5E5CC.7020403@dc.baikal.ru> (raw)
In-Reply-To: <7386.1204871746@sss.pgh.pa.us>
References: <47D0D7B0.7060109@dc.baikal.ru>
<7386.1204871746@sss.pgh.pa.us>
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.
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: arhipov@dc.baikal.ru, tgl@sss.pgh.pa.us
Subject: Re: Nested loop vs merge join: inconsistencies between estimated and actual time
In-Reply-To: <47D5E5CC.7020403@dc.baikal.ru>
* 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