2010/11/8 Mark Rostron <mrostron@xxxxxxx>: >> > >> > What is the procedure that postgres uses to decide whether or not a >> > table/index block will be left in the shared_buffers cache at the end >> > of the operation? >> > >> >> The only special cases are for sequential scans and VACUUM, which use continuously re-use a small section of the buffer cache in some cases instead. > > Thanks - the part about sequential scans and the re-use of a small section of shared_buffers is the bit I was interested in. > I don't suppose you would be able to tell me how large that re-useable area might be? There are 256KB per seqscan and 256KB per vacuum. I suggest you to go reading src/backend/storage/buffer/README > > Now, with regard to the behavior of table sequential scans: do the stat values in seq_scan and seq_tup_read reflect actual behavior. > I assume they do, but I'm just checking - these would be updated as the result of real I/O as opposed to fuzzy estimates? They represent the real stat for hit/read from shared_buffers, *not* from OS buffers. Getting real statistic from OS has a cost because postgresql don't use (for other reason) mmap to get data. > > Obviously, the reason I am asking this is that I am noticing high machine io levels that would only result from sequential scan activity You may want to start inspect your postgresql buffer cache with the contrib module pg_buffercache. http://www.postgresql.org/docs/9.0/static/pgbuffercache.html Then if it is not enough you can inspect more precisely your OS cache with pgfincore but it migh be useless in your situation. http://villemain.org/projects/pgfincore > The explain output says otherwise, but the seq_scan stat value for the table kinda correlates. Starting with 9.0, the contrib module pg_stat_statements provide a lot of information about buffer access (from shared buffers usage, but still very valuable information) you should have a look at it if you have such postgresql version installed. -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ ; PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
