From: Ivan Voras <ivoras@gmail.com>
To: Greg Smith <greg@2ndquadrant.com>
To: Ivan Voras <ivoras@freebsd.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query performance with disabled hashjoin and mergejoin
Date: Fri, 04 Feb 2011 16:13:19 +0100
Message-ID: <e6e9216e-9330-4b91-bbe6-c6baab0778d6@email.android.com> (raw)
In-Reply-To: <4D4C10EA.2080901@2ndquadrant.com>
References: <iigq7o$3ub$1@dough.gmane.org>
<4D4C10EA.2080901@2ndquadrant.com>
Sorry for the misunderstaning: of course not default "normal" settings; shared buffers, work mem, wal segments and others have been tuned according to available hardware (e.g. 4 GB, 32 MB, 10 for these settings, respectively). I meant "planner default settings" in the post.
--
Sent from my Android phone, please excuse my brevity.
Greg Smith <greg@2ndquadrant.com> wrote:
Ivan Voras wrote: > The "vanilla" plan, with default settings is: Pause here for a second: why default settings? A default PostgreSQL configuration is suitable for systems with about 128MB of RAM. Since you say you have "good enough hardware", I'm assuming you have a bit more than that. The first things to try here are the list at http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server ; your bad query here looks like it might benefit from a large increase to effective_cache_size, and possibly an increase to work_mem as well. Your "bad" plan here is doing a lot of sequential scans instead of indexed lookups, which makes me wonder if the change in join types you're forcing isn't fixing that part as a coincidence. Note that the estimated number of rows coming out of each form of plan is off by a factor of about 200X, so it's not that the other plan type is better estimating anything. -- Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Serv
ices,
and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
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: ivoras@gmail.com, greg@2ndquadrant.com, ivoras@freebsd.org
Subject: Re: Query performance with disabled hashjoin and mergejoin
In-Reply-To: <e6e9216e-9330-4b91-bbe6-c6baab0778d6@email.android.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