From: Kenneth Marshall <ktm@rice.edu>
To: Anj Adu <fotographs@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andy Colson <andy@squeakycode.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: slow query performance
Date: Fri, 11 Jun 2010 07:44:24 -0500
Message-ID: <20100611124424.GO3063@aart.is.rice.edu> (raw)
In-Reply-To: <AANLkTikuvlQCq2FUyOPYfmh_A2by39rMsIB3vDRCbaUk@mail.gmail.com>
References: <AANLkTilbM1iWSrrchOB9x1_W2CKQSTzCow6BznV1DxNk@mail.gmail.com>
<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>
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
>
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: ktm@rice.edu, fotographs@gmail.com, robertmhaas@gmail.com, tgl@sss.pgh.pa.us, andy@squeakycode.net
Subject: Re: slow query performance
In-Reply-To: <20100611124424.GO3063@aart.is.rice.edu>
* 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