Re: Do all Postgres queries touch Shared_Buffers at some point?

On Sun, Dec 29, 2013 at 9:05 PM, Shiv Sharma <shiv(dot)sharma(dot)1835(at)gmail(dot)com> wrote:
> I am puzzled about the extent to which shared_bufferes is used for different
> queries. Do _all_ queries "touch" shared buffers at some point of their
> execution?
>
> Many of our warehouse queries are seq_scan followed by HASH. I know
> work_mem is assigned for HASH joins: but does this mean that these queries
> never touch shared buffers at *all* during their execution? Perhaps they
> are read into shared_buffers and then passed into work_mem HASH areas???
>
> What about updates on big tables? What about inserts on big tables? What
> about append-inserts?
>
> I think I could get these answers from Explain Analyze Buffers but I am on
> 8.2 :-(
>
> Please tell me which queries use/touch shared_buffers in general terms, or
> please point me to documentation.
All your queries that interacts with relations.

shared_buffers is used for data caching across all the backends of the
server, to put it simply pages of the relation involved. Such data can
be relation data, like data of a table you defined yourself, index
data, or some system catalog data, containing definitions of the
database objects. So simply everything that is a relation and contains
physical data might be in shared buffers. Views for example do not
enter in this category.

You could for example use pg_buffercache to have a look at what
contains the shared buffers:
http://www.postgresql.org/docs/devel/static/pgbuffercache.html

Regards,
--
Michael