From: Julien Cigar <jcigar@ulb.ac.be>
To: Kevin Grittner <kgrittn@ymail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: slow joins?
Date: Sat, 06 Apr 2013 16:43:50 +0200
Message-ID: <516034A6.3030703@ulb.ac.be> (raw)
In-Reply-To: <1365258149.7784.YahooMailNeo@web162906.mail.bf1.yahoo.com>
References: <CACfv+pLwqw-c=kR65WjA_pFy9M2qoKM2Usy77BoD8QWNwcfGHA@mail.gmail.com>
<CACfv+pKBGQ3SG=9u4bVzVo5GHZW8DyO=-s2LMUv2VFfCNaKTwQ@mail.gmail.com>
<515FFEB1.4040606@ulb.ac.be>
<1365258149.7784.YahooMailNeo@web162906.mail.bf1.yahoo.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 04/06/2013 16:22, Kevin Grittner wrote:
> 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. 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. This setting does
> not allocate RAM, but tells the planner how likely it is to find
> things in cache for, say, repeated index access. A higher setting
> makes the random access involved in index scans seem like less of a
> problem.
I agree that the very first thing to check is effective_cache_size
> random_page_cost: You seem to have a very high cache hit ratio,
> between shared_buffers and the OS cache. To model this you should
> decrease random_page_cost to something just above seq_page_cost or
> equal to it. 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. 0.1 is a not-uncommon
> setting for instances with the active portion of the database
> well-cached.
I would first raise cpu_tuple_cost rather than touch random_page_cost.
Raising cpu_tuple_cost is
a more "fine-grained method" for discouraging seqscans than
random_page_cost is.
> 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. In combination with the
> above changes I've never had to go beyond 0.03 to get a good plan.
> 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. I've never tried higher
> than 0.05, so I can't speak to that.
Yep, default cpu_tuple_cost is just too low ..
> In any event, your current cost settings aren't accurately modeling
> actual costs in your environment for your workload. 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.
>
--
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: jcigar@ulb.ac.be, kgrittn@ymail.com
Subject: Re: slow joins?
In-Reply-To: <516034A6.3030703@ulb.ac.be>
* 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