On Tue, Dec 16, 2014 at 4:18 PM, Cristian Iturrieta <[email protected]> wrote: > > Dear pgadmin-hackers, > > > > I have the following query: What could be due to a single query sql one > day run fast and another slow. >
Hi Cristian. Since your question concerns the PostgreSQL database and not the pgAdmin client (they are separate efforts), it would be more appropriately asked in the Postgres General list: < [email protected]>. I believe you will have greater success getting answers there. > 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 > > > > [image: BBR_logotipo] > > *Cristian Iturrieta Olivares **| Área Ingeniería* > > > *CONSULTOR **Luis Thayer Ojeda Nº 1145* > > > *Providencia - Santiago de Chile **(** +56 2** 2**8404250 | www.bbr.cl > <http://www.bbr.cl>* > > >
