To: Michael Stone <mstone+postgres@mathom.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Mostly read performance
References: <42FBCF4D.6060002@comcast.net>
	<42FBD377.8040006@arbash-meinel.com> <42FBDB97.1010906@comcast.net>
	<20050812003031.GP19080@mathom.us>
In-Reply-To: <20050812003031.GP19080@mathom.us>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 12 Aug 2005 03:32:37 -0400
Message-ID: <871x4za9uy.fsf@stark.xeocode.com>
Lines: 25
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii


Michael Stone <mstone+postgres@mathom.us> writes:

> > Well, that's what you'd expect.  But a first time 70MB fetch on a freshly
> > rebooted system took just as long as all secondary times.  (Took over a
> > minute to fetch, which is too long for my needs, at least on secondary
> > attempts).

That's not impressively fast even for the disk. You should get up to about
40Mbit/s or 5MByte/s from the disk. Add some overhead for postgres; so I would
expect a full table scan of 70MB to take more like 15-30s, not over a minute.

What is your shared_buffers setting? Perhaps you have it set way too high or
way too low?

Also, you probably should post the "explain analyze" output of the actual
query you're trying to optimize. Even if you're not looking for a better plan
having hard numbers is better than guessing.

And the best way to tell if the data is cached is having a "vmstat 1" running
in another window. Start the query and look at the bi/bo columns. If you see
bi spike upwards then it's reading from disk.

-- 
greg