DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=ymail.com;
	h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=dD4bt6bRAMrjb4A7BFAcBz4aT2e4RZS1lDd7ixHSqLHtsLOw1V5aYljKVX7U6f3B3tP9onHhksQih8Bt2J1WWU0M0v30r9mIF6O/aQe/JRCo2rHB9TM8eNJqlWBdBVaKJVvCV6fojU7dBC03aiuhcJ2+E6tFz1Przs+TvLibP8o=;
References: 
 <CACfv+pLwqw-c=kR65WjA_pFy9M2qoKM2Usy77BoD8QWNwcfGHA@mail.gmail.com>
	<CACfv+pKBGQ3SG=9u4bVzVo5GHZW8DyO=-s2LMUv2VFfCNaKTwQ@mail.gmail.com>
	<515FFEB1.4040606@ulb.ac.be>
Message-ID: <1365258149.7784.YahooMailNeo@web162906.mail.bf1.yahoo.com>
Date: Sat, 6 Apr 2013 07:22:29 -0700 (PDT)
From: Kevin Grittner <kgrittn@ymail.com>
Reply-To: Kevin Grittner <kgrittn@ymail.com>
Subject: Re: slow joins?
To: Julien Cigar <jcigar@ulb.ac.be>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
In-Reply-To: <515FFEB1.4040606@ulb.ac.be>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Julien Cigar <jcigar@ulb.ac.be> wrote:

> try to increase cpu_tuple_cost to 0.1

I agree that's on the right track, but possibly an overly blunt
tool for the job.=A0 The following settings are likely to need
adjustment, IMO:

effective_cache_size: People often set this to somewhere in the
range of 50% to 75% of the RAM on the machine.=A0 This setting does
not allocate RAM, but tells the planner how likely it is to find
things in cache for, say, repeated index access.=A0 A higher setting
makes the random access involved in index scans seem like less of a
problem.

random_page_cost: You seem to have a very high cache hit ratio,
between shared_buffers and the OS cache.=A0 To model this you should
decrease random_page_cost to something just above seq_page_cost or
equal to it.=A0 To reflect the relatively low cost of reading a page
from the OS cache (compared to actually reading from disk) you
might want to reduce both of these below 1.=A0 0.1 is a not-uncommon
setting for instances with the active portion of the database
well-cached.

cpu_tuple_cost: I always raise this; I think our default is just
too low to accurately model the cost of reading a row, compared to
the cost factors used for other things.=A0 In combination with the
above changes I've never had to go beyond 0.03 to get a good plan.=20
I've pushed it to 0.05 to see if that put me near a tipping point
for a bad plan, and saw no ill effects.=A0 I've never tried higher
than 0.05, so I can't speak to that.

In any event, your current cost settings aren't accurately modeling
actual costs in your environment for your workload.=A0 You need to
adjust them.

One of the estimates was off, so increasing the statistics sample
size might help, but I suspect that you need to make adjustments
like the above in any event.

--=20
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance