Hi, Thanks for your response. I've checked it again and found that the main cause is the execution of ANALYZE. As I have mentioned, I have two tables: table A is a big one (around 10M~100M records) for log data and table B is a small one (around 1k records) for keeping some current status. There are a lot of update operations and some search operations on the table B. For the performance issue, I would like to keep table B as compact as possible. According your suggestion, I try to invoke standard vacuum (not full) more frequently (e.g., once per min). However, when I analyze the table A, the autovacuum or vacuum on the table B cannot find any removable row version (the number of nonremoveable row versions and pages keeps increasing). After the analysis finishes, the search operations on the table B is still inefficient. If I call full vacuum right now, then I can have quick response time of the search operations on the table B again. Any suggestions for this situation? On Tue, Nov 16, 2010 at 11:26 PM, Alvaro Herrera <alvherre@xxxxxxxxxxxxxxxxx> wrote: > Excerpts from kuopo's message of vie nov 12 05:01:24 -0300 2010: >> Hi, >> >> I have a question about the behavior of autovacuum. When I have a big >> table A which is being processed by autovacuum, I also manually use >> (full) vacuum to clean another table B. Then I found that I always got >> something like “found 0 removable, 14283 nonremovable row”. However, >> if I stop the autovacuum functionality and use vacuum on that big >> table A manually, I can clean table B (ex. found 22615 removable, 2049 >> nonremovable row). >> >> Is this correct? Why do vacuum and autovacuum have different actions? > > Vacuum full does not assume that it can clean up tuples while other > transactions are running, and that includes the (non full, or "lazy") > vacuum that autovacuum is running. Autovacuum only runs lazy vacuum; > and that one is aware that other concurrent vacuums can be ignored. > > Just don't use vacuum full unless strictly necessary. It has other > drawbacks. > > -- > Álvaro Herrera <alvherre@xxxxxxxxxxxxxxxxx> > The PostgreSQL Company - Command Prompt, Inc. > PostgreSQL Replication, Consulting, Custom Development, 24x7 support > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
