2009/9/14 <zz_11@xxxxxxx>: > Цитат от Robert Haas <robertmhaas@xxxxxxxxx>: > >> 2009/9/14 <tv@xxxxxxxx>: >>> >>> It seems there's something very wrong - the plans are "equal" but in the >>> first case the results (actual time) are multiplied by 100. Eithere there >>> is some sort of cache (so the second execution is much faster), or the >>> system was busy during the first execution, or there is something wrong >>> with the hardware. >> >> I think you should run this query more than twice. If it's slow the >> first time and fast every time for many executions after that, then >> it's probably just the data getting loaded into the OS cache (or >> shared buffers). If it's bouncing back and forth between fast and >> slow, you might want to check whether your machine is swapping. > > I did it many times. Alter the first atempt it works fast, but after a > couple of minutes ( I think after changing the data in cache) the query is > working also very slow. > > I do not see any swap on OS. > >> >> It might also be helpful to post all the uncommented settings from >> your postgresql.conf file. > > postgresql.conf : > > max_connections = 2000 > shared_buffers = 1800MB > temp_buffers = 80MB > work_mem = 120MB > > maintenance_work_mem = 100MB > max_fsm_pages = 404800 > max_fsm_relations = 5000 > > max_files_per_process = 2000 > wal_buffers = 64MB > checkpoint_segments = 30 > effective_cache_size = 5000MB > default_statistics_target = 800 I think you're exhausting the physical memory on your machine. How much RAM do you have? How many active connections at one time? 120MB is a HUGE value for work_mem. I would try reducing that to, say, 4 MB, and see what happens. Your setting for temp_buffers also seems way too high. I would put that one back to the default, at least for starters. And for that matter, why have you increased the value for wal_buffers to over 1000 times the default value? The reason you may not be seeing evidence of swapping is that it may be happening quite briefly during query execution. But I have to think it's happening, because otherwise the performance drop-off is hard to account for. ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
