Postgresql 9.2, Memoy cache usage.

Dear pgadmin-hackers,

I have the following query: What could be due to a single query sql one
day run fast and another slow. The database has massive loads overnight and
the next day serves datawarehouse. This causes the first query is slower by
having to go find all the information to disk. With a new process is
achieved load data from disk cache operating system that is able to improve
response times. I have doubts as to refresh cache and as the LRU (least
recently used) works. One way to optimize the query was generating "Common
Table Expressions", here is my other question, where are CTE's stored?,
Work_mem?, Buffer cache?, Temporary tablespace ?, etc ..

Deputy some parameter values postgresql :

max_connections = 40

checkpoint_completion_target = 0.9

random_page_cost=20

seq_page_cost = 20

default_statistics_target = 100 # pgtune wizard 2014-11-20

maintenance_work_mem = 1GB # pgtune wizard 2014-11-20

constraint_exclusion = on # pgtune wizard 2014-11-20

effective_cache_size = 30GB # pgtune wizard 2014-11-20

work_mem = 800MB # pgtune wizard 2014-11-20

wal_buffers = 32MB # pgtune wizard 2014-11-20

checkpoint_segments = 64 # pgtune wizard 2014-11-20

shared_buffers = 5GB # pgtune wizard 2014-11-20

Summary charging procedure cache operating system:

select distinct relfilenode from pg_buffercache > /tmp/cacheprecarga.lst

cat /tmp/cacheprecarga.lst | while read line; do find
/var/lib/pgsql/9.2/data/base/ -name $line -print -exec cat '{}' > /dev/null
\; ; done

regards

BBR_logotipo

Cristian Iturrieta Olivares | Área Ingeniería

CONSULTOR
Luis Thayer Ojeda Nº 1145

Providencia - Santiago de Chile
( +56 2 28404250 | <http://www.bbr.cl> www.bbr.cl