Re: optimizing selects on time-series data in Pg

On Fri, Aug 01, 2003 at 10:33:42AM -0700, Josh Berkus wrote:
> From the look of it, the database is doing the best it can on your hardware
> and current tuning setup. I don't think you can speed this up on the SQL
> side, unless you implement an aggregate caching scheme.
>
> It's possible that you could move the break point btw. seq scan and index scan
> slightly by increasing shared_buffers and effective_cache_size. What are
> those variables now? And what's your hardware?

from my postgresql.conf:

max_connections is 64

shared_buffers = 128 # 2*max_connections, min 16 (default 64)
...
#effective_cache_size = 1000 # default in 8k pages

The zp_log table is the only one of this size in this database and the rest of
the tables run the gamut of uses so leaving effective_cache_size as default
seemed the safest thing to do to avoid over-optimization...but maybe it's time
to break out of that.

There are various other processes running on this machine, but it never swaps
to disk. This is the first circumstance I've found where I've run into a
bottleneck I couldn't optimize around easily, so although I was hoping that I
was missing something obvious, I could move the db (maybe just the table) to a
dedicated machine and up the effective_cache_size.

PIII-800/512MB RAM running FreeBSD 4-STABLE with the database data files NFS
mounted via a 100Mbps/full-duplex private network from a lightly-loaded
Network Appliance F87 fileserver (I'm pretty certain that we're not IO bound).

Thanks,
Aditya