Date: Fri, 11 Jun 2010 08:28:17 -0500
From: Kenneth Marshall <ktm@rice.edu>
To: Anj Adu <fotographs@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	Andy Colson <andy@squeakycode.net>, pgsql-performance@postgresql.org
Subject: Re: slow query performance
Message-ID: <20100611132817.GQ3063@aart.is.rice.edu>
References: <4C07F7C9.3060401@squeakycode.net>
	<AANLkTin5Ukiz3JfELSuUEDDk1VvRfmmx913-bXer06NA@mail.gmail.com>
	<AANLkTilD4kN8bc7I3XauXEaryvsEZ-mXebx5yk71Vv6r@mail.gmail.com>
	<5860.1276138520@sss.pgh.pa.us>
	<AANLkTilVl4gsq7dHFjy6BZ9clP6V9Rqgb6u-5rzDcEBc@mail.gmail.com>
	<AANLkTilaSzbOcehwuWtA3FPunLgGwqHZUh9bu5VBuuBX@mail.gmail.com>
	<AANLkTikb6ap3kMKVoatB3nT5xQeir61sA920NicU2AGm@mail.gmail.com>
	<AANLkTikuvlQCq2FUyOPYfmh_A2by39rMsIB3vDRCbaUk@mail.gmail.com>
	<20100611124424.GO3063@aart.is.rice.edu>
	<AANLkTilkmQY_Uh_mi2qogbqM88VhZI_Re_tmupWEOdxM@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <AANLkTilkmQY_Uh_mi2qogbqM88VhZI_Re_tmupWEOdxM@mail.gmail.com>
User-Agent: Mutt/1.5.16 (2007-06-09)

If you check the archives, you will see that this is not easy
to do because of the effects of caching. The default values
were actually chosen to be a good compromise between fully
cached in RAM and totally un-cached. The actual best value
depends on the size of your database, the size of its working
set, your I/O system and your memory. The best recommendation
is usually to use the default values unless you know something
about your system that moves it out of that arena.

Regards,
Ken

On Fri, Jun 11, 2010 at 06:23:31AM -0700, Anj Adu wrote:
> Is there a way to determine a reasonable value for random_page_cost
> via some testing with OS commands. We have several postgres databases
> and determining this value on a case by case basis may not be viable
> (we may have to go with the defaults)
> 
> On Fri, Jun 11, 2010 at 5:44 AM, Kenneth Marshall <ktm@rice.edu> wrote:
> > Hi Anj,
> >
> > That is an indication that your system was less correctly
> > modeled with a random_page_cost=2 which means that the system
> > will assume that random I/O is cheaper than it is and will
> > choose plans based on that model. If this is not the case,
> > the plan chosen will almost certainly be slower for any
> > non-trivial query. You can put a 200mph speedometer in a
> > VW bug but it will never go 200mph.
> >
> > Regards,
> > Ken
> >
> > On Thu, Jun 10, 2010 at 07:54:01PM -0700, Anj Adu wrote:
> >> I changed random_page_cost=4 (earlier 2) and the performance issue is gone
> >>
> >> I am not clear why a page_cost of 2 on really fast disks would perform badly.
> >>
> >> Thank you for all your help and time.
> >>
> >> On Thu, Jun 10, 2010 at 8:32 AM, Anj Adu <fotographs@gmail.com> wrote:
> >> > Attached
> >> >
> >> > Thank you
> >> >
> >> >
> >> > On Thu, Jun 10, 2010 at 6:28 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> >> >> On Wed, Jun 9, 2010 at 11:17 PM, Anj Adu <fotographs@gmail.com> wrote:
> >> >>> The plan is unaltered . There is a separate index on theDate as well
> >> >>> as one on node_id
> >> >>>
> >> >>> I have not specifically disabled sequential scans.
> >> >>
> >> >> Please do "SHOW ALL" and attach the results as a text file.
> >> >>
> >> >>> This query performs much better on 8.1.9 on a similar sized
> >> >>> table.(althought the random_page_cost=4 on 8.1.9 and 2 on 8.4.0 )
> >> >>
> >> >> Well that could certainly matter...
> >> >>
> >> >> --
> >> >> Robert Haas
> >> >> EnterpriseDB: http://www.enterprisedb.com
> >> >> The Enterprise Postgres Company
> >> >>
> >> >
> >>
> >> --
> >> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-performance
> >>
> >
>