From: Kevin Grittner <kgrittn@mail.com>
To: Huan Ruan <leohuanruan@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: hash join vs nested loop join
Date: Wed, 12 Dec 2012 18:47:32 -0500
Message-ID: <20121212234732.80090@gmx.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Huan Ruan wrote:
> is a lot slower than a nested loop join.
Giving actual numbers is more useful than terms like "a lot". Even
better is to provide the output of EXPLAIN ANALYZZE rather than
just EXPLAIN. This shows estimates against actual numbers, and give
timings. For more suggestions see this page:
http://wiki.postgresql.org/wiki/SlowQueryQuestions> I don't understand why the optimiser chooses the hash join in
> favor of the nested loop. What can I do to get the optimiser to
> make a better decision (nested loop in this case)? I have run
> analyze on both tables.
> Config changes are
>
> - shared_buffers = 6GB
> - effective_cache_size = 18GB
> - work_mem = 10MB
> - maintenance_work_mem = 3GB
As already suggested, there was a change made in 9.2 which may have
over-penalized nested loops using index scans. This may be fixed in
the next minor release.
Also, as already suggested, you may want to reduce random_page
cost, to bring it in line with the actual cost relative to
seq_page_cost based on your cache hit ratio.
Additionally, I just routinely set cpu_tuple_cost higher than the
default of 0.01. I find that 0.03 to 0.05 better models the actual
relative cost of processing a tuple.
-Kevin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: kgrittn@mail.com, leohuanruan@gmail.com
Subject: Re: hash join vs nested loop join
In-Reply-To: <20121212234732.80090@gmx.com>
* 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