Re: strange performance regression between 7.4 and 8.1

On 3/2/07, Ron <rjpeace(at)earthlink(dot)net> wrote:
> At 11:03 AM 3/2/2007, Alex Deucher wrote:
> >On 3/2/07, Ron <rjpeace(at)earthlink(dot)net> wrote:
> >
> >>May I suggest that it is possible that your schema, queries, etc were
> >>all optimized for pg 7.x running on the old HW?
> >>(explain analyze shows the old system taking ~1/10 the time per row
> >>as well as estimating the number of rows more accurately)
> >>
> >>RAM is =cheap=. Much cheaper than the cost of a detective hunt
> >>followed by rework to queries, schema, etc.
> >>Fitting the entire DB into RAM is guaranteed to help unless this is
> >>an OLTP like application where HD IO is required to be synchronous..
> >>If you can fit the entire DB comfortably into RAM, do it and buy
> >>yourself the time to figure out the rest of the story w/o impacting
> >>on production performance.
> >
> >Perhaps so. I just don't want to spend $1000 on ram and have it only
> >marginally improve performance if at all. The old DB works, so we can
> >keep using that until we sort this out.
> >
> >Alex
> 1= $1000 worth of RAM is very likely less than the $ worth of, say,
> 10 hours of your time to your company. Perhaps much less.
> (Your =worth=, not your pay or even your fully loaded cost. This
> number tends to be >= 4x what you are paid unless the organization
> you are working for is in imminent financial danger.)
> You've already put more considerably more than 10 hours of your time
> into this...
>
> 2= If the DB goes from not fitting completely into RAM to being
> completely RAM resident, you are almost 100% guaranteed a big
> performance boost.
> The exception is an OLTP like app where DB writes can't be done
> a-synchronously (doing financial transactions, real time control systems, etc).
> Data mines should never have this issue.
>
> 3= Whether adding enough RAM to make the DB RAM resident (and
> re-configuring conf, etc, appropriately) solves the problem or not,
> you will have gotten a serious lead as to what's wrong.
>
> ...and I still think looking closely at the actual physical layout of
> the tables in the SAN is likely to be worth it.
>

How would I go about doing that?

Thanks,

Alex