Re: Oracle v. Postgres 9.0 query performance

Tony Capobianco <tcapobianco@prospectiv.com> wrote:

> According to some documentation, I want to set
> effective_cache_size to my OS disk cache + shared_buffers.

That seems reasonable, and is what has worked well for me.

> In this case, I have 4 quad-core processors with 512K cache (8G)
> and my shared_buffers is 7680M.  Therefore my effective_cache_size
> should be approximately 16G?

I didn't follow that at all.  Can you run `free` or `vmstat`?  If
so, go by what those say your cache size is.

> Most of our other etl processes are running fine, however I'm
> curious if I could see a significant performance boost by reducing
> the effective_cache_size.

Since it is an optimizer costing parameter and has no affect on
memory allocation, you can set it on a connection and run a query on
that connection to test the impact.  Why wonder about it when you
can easily test it?

-Kevin