Re: [PERFORM] Memory question on win32 systems

Merlin Moncure wrote:
> On Thu, May 29, 2008 at 10:19 AM, Justin <justin(at)emproshunts(dot)com> wrote:
>
>> To my understanding Postgresql only caches queries and results in memory for
>> that specific connection. So when that connection is closed those cached
>> results are cleared out. So cached indexs and queries are for that
>> connection only. I hope my understanding is correct.
>>
>
> completely wrong.
>
> They are called 'shared' buffers for a reason. Also you are missing
> the point of the o/s file cache which lies under that. If you have a
> computer with 4gb ram that is addressable, all its memory is used for
> caching at all times, period, less what operating system needs or what
> is used by running programs or temporary demands (sorting, etc).
>
> Also, postgresql doesn't as a rule cache 'results and queries'.
>
> shared buffers setting reserves memory for postgresql's internal cache
> (plus some housekeeping things like locks)...which can be faster than
> the o/s cache because it is more tightly integrated with the backend.
> However a page fault to disk is much more interesting in performance
> terms than the performance differences between shared buffers and o/s
> cache.
>
> merlin
>
>
This is not meant to be argumentative i'm trying to clearify what is
going because documentation for different sources seem to be in
conflict or confusing.
---------------------------------------------------
From Postgresql Second Edition:
Shared_Buffers: This cache is shared by all clients connected to a
single cluster. DISK I?) (and cache I/O) is performed in 8KB chunks.
The shared_buffers parameter determines how many 8KB will be created in
the shared cache.

from here explanation is different
http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html
it referrs to queries which means to me the SQL command and the result
sets (but that is wrong)

We have sort_mem aka work_mem
---------------------------------------------------
From Postgresql Second Edition:
where postgresql processes query it transforms the query from
string form into an execution plan. An execution plan is a sequence of
operations that must be performed in order satisfy the query.

(This specific to the client connection and when the client is closed
out this is cleared my thinking.)
----------------------------------------------------
From postgresql Documentation

work_mem (integer)

Specifies the amount of memory to be used by internal sort
operations and hash tables before switching to temporary disk files.
The value is defaults to one megabyte (1MB). Note that for a complex
query, several sort or hash operations might be running in parallel;
each one will be allowed to use as much memory as this value
specifies before it starts to put data into temporary files. Also,
several running sessions could be doing such operations
concurrently. So the total memory used could be many times the value
of work_mem; it is necessary to keep this fact in mind when choosing
the value. Sort operations are used for ORDER BY, DISTINCT, and
merge joins. Hash tables are used in hash joins, hash-based
aggregation, and hash-based processing of IN subqueries

---------------------------------------------------------

Quoting You "Also, postgresql doesn't as a rule cache 'results and queries'.

Then what is the purpose of shared buffers if nothing is being reused
is it only used to keep track locks, changes and what is to being
spooled to the kernel???

I'm confussed trying to figure out how caches are being use and being
moving through postgresql backend. As many have stated that small
database can fit completely in the caches How does that really work.