Re: Questions on query planner, join types, and work_mem - Mailing list pgsql-performance

From Kevin Grittner
Subject Re: Questions on query planner, join types, and work_mem
Date
Msg-id 4C592C6F02000025000341BA@gw.wicourts.gov
Whole thread Raw
In response to Re: Questions on query planner, join types, and work_mem  (Peter Hussey <peter@labkey.com>)
List pgsql-performance
Peter Hussey <peter@labkey.com> wrote:

> I already had effective_cache_size set to 500MB.

That seems awfully small.  You do realize that this setting does not
cause PostgreSQL to allocate any memory; it merely advises how much
disk space is likely to be cached.  It should normally be set to the
sum of your shared_buffers setting and whatever your OS reports as
cached.  Setting it too small will discourage the optimizer from
picking plans which use indexes.

> I experimented with lowering  random_page_cost to 3 then 2.

As others have said, in a fully cached system that's still too high.
If the active portion of your database is fully cached, you should
set random_page_cost and seq_page_cost to the same value, and that
value should probably be in the range of 0.1 to 0.005.  It can get
trickier if the active portion is largely but not fully cached; we
have one server where we found, through experimentation, that we got
better plans overall with seq_page_cost = 0.3 and random_page_cost =
0.5 than any other settings we tried.

-Kevin

pgsql-performance by date:

Previous
From: Robert Haas
Date:
Subject: Re: Strange explain on partitioned tables
Next
From: "Kevin Grittner"
Date:
Subject: LIKE without wildcard different from =