Re: Mostly read performance

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