Excerpts from kuopo's message of jue nov 18 04:10:36 -0300 2010: > 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. Hmm, I don't think we can optimize the analyze-only operation the same way we optimize vacuum (i.e. allow vacuum to proceed while it's in progress). Normally analyze shouldn't take all that long anyway -- why is it that slow? Are you calling it in a transaction that also does other stuff? Are you analyzing more than one table in a single transaction, perhaps even the whole database? Perhaps you could speed it up by lowering vacuum_cost_delay, if it's set to a nonzero value. -- Ã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
