Re: Query help - Mailing list pgsql-performance

From Kevin Grittner
Subject Re: Query help
Date
Msg-id 4A7705DE02000025000292CB@gw.wicourts.gov
Whole thread Raw
In response to Re: Query help  ("Subbiah Stalin-XCGF84" <SSubbiah@Motorola.com>)
Responses Re: Query help  ("Subbiah Stalin-XCGF84" <SSubbiah@Motorola.com>)
List pgsql-performance
"Subbiah Stalin-XCGF84" <SSubbiah@Motorola.com> wrote:

> Shared buffer=8G, effective cache size=4G.

That is odd; if your shared buffers are at 8G, you must have more than
4G of cache.  How much RAM is used for cache at the OS level?
Normally you would add that to the shared buffers to get your
effective cache size, or at least take the larger of the two.

How much RAM is on this machine in total?  Do you have any other
processes which use a lot of RAM or might access a lot of disk from
time to time?

> Let me know if you need any other information.

The \d output for the object table, or the CREATE for it and its
indexes, would be good.  Since it's getting through the random reads
by the current plan at the rate of about one every 5ms, I'd say your
drive array is OK.  If you want to make this query faster you've
either got to have the data in cache or it has to have reason to
believe that a different plan is faster.

One thing which might help is to boost your work_mem setting to
somewhere in the 32MB to 64MB range, provided that won't drive you
into swapping.  You could also try dropping the random_page_cost to
maybe 2 to see if that gets you a different plan.  You can do a quick
check on what plans these generate by changing them on a given
connection and then requesting just an EXPLAIN of the plan, to see if
it's different.  (This doesn't actually run the query, so it's fast.)

-Kevin

pgsql-performance by date:

Previous
From: "Subbiah Stalin-XCGF84"
Date:
Subject: Re: Query help
Next
From: Merlin Moncure
Date:
Subject: Re: PostgreSQL 8.4 performance tuning questions