From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrey Lizenko <lizenko79@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: query plan question, nested loop vs hash join
Date: Sun, 05 Oct 2014 15:47:52 -0400
Message-ID: <24854.1412538472@sss.pgh.pa.us> (raw)
In-Reply-To: <CADKuZZCntAT39dbdERHcD2DrXi_Dt3oAhBhP98GfcGC=hfhXbA@mail.gmail.com>
References: <CADKuZZCntAT39dbdERHcD2DrXi_Dt3oAhBhP98GfcGC=hfhXbA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Andrey Lizenko <lizenko79@gmail.com> writes:
> What is the reason of "Seq Scan on activities_example" in the first case?
> Is it possible to force optimizer choose the second plan without doing
> "set enable_hashjoin = off;" ?
Disabling hashjoins altogether would be a pretty dangerous "fix".
I think the real issue here is that you have an entirely cached-in-memory
database and therefore you ought to reduce random_page_cost. The
planner's estimates for the first query seem to more or less match reality
(on the assumption that 1 msec equals about 100 cost units on your
machine). The cost estimates for the second one are way off though,
mainly in that the repeated indexscans are far cheaper than the planner
thinks. Getting that cost estimate down requires reducing random_page_cost
or increasing effective_cache_size or some combination.
You can find the conventional wisdow about this sort of thing at
https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
regards, tom lane
--
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: tgl@sss.pgh.pa.us, lizenko79@gmail.com
Subject: Re: query plan question, nested loop vs hash join
In-Reply-To: <24854.1412538472@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