604 CHAPTER 26 POSTGRESQL ADMINISTRATION The ANALYZEcommand (Web hosting plans)
604 CHAPTER 26 POSTGRESQL ADMINISTRATION The ANALYZEcommand can be called at the database level, where all tables are analyzed, at the table level, where a single table is analyzed, or even at the column level, where a single column on a specific table is analyzed. In all cases, PostgreSQL examines the table to determine various pieces of statistical information and stores that information in the pg_statistic table. On larger tables, ANALYZE only looks at a small, statistical sample of the table, allowing even very large tables to be analyzed in a relatively short period of time. Also, ANALYZE only requires a read lock on the current table being analyzed, so it is possible to run ANALYZEwhile concurrent oper ations are happening within the database. The VERBOSE option outputs a progress report and a summary of the statistical information collected. The recommended practice is to run ANALYZE at regular intervals, with the length between analyzing based on how frequently (or infrequently) the statistical makeup of the table changes due to new inserts, updates, or deletes on the data within a table. Autovacuum In versions prior to PostgreSQL 8.1, the execution of VACUUM and ANALYZE commands had to be managed manually, or with an extra autovacuum process. Beginning in version 8.1, this automated process has been integrated into the PostgreSQL core code, and can be enabled by setting the autovacuum parameter to TRUE in the postgresql.conf file. When autovacuum is enabled, PostgreSQL will launch an additional server process to peri odically connect to each database in the system and review the number of inserted, updated, or deleted rows in each table to determine if a VACUUMor ANALYZEcommand should be run. The frequency of these checks can be controlled through the use of the autovacuum_naptime setting in the postgresql.conf file. PostgreSQL starts by vacuuming any databases that are close to transaction ID wraparound. However, if there is no database that meets that criterion, PostgreSQL vacuums the database that was processed least recently. In addition to controlling how often each database is checked, you can control under which criteria a given table will be vacuumed or analyzed. The primary way of setting this criteria is through the autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor settings for vacuuming and the autovacuum_analyze_threshold and autovacuum_analyze_scale_factor settings for analyzing, all of which are found in the postgresql.conf file. The autovacuum process uses these settings to create a vacuum threshold for each table, based on the following formula: vacuum base threshold + (vacuum scale factor number of tuples) = vacuum threshold While these settings will be applied on a global basis, you can also set these parameters for individual tables in the pg_autovacuum system table. This table allows you to enter a row for each table in your database and set individual base threshold and scale factor settings for those tables, or even to disable running VACUUMor ANALYZE commands on given tables as needed. One reason you might want to disable running VACUUM or ANALYZE commands on a table would be that a table has a narrowly defined use (for example, strictly for inserts only), where the statistics of the data involved are not likely to change much over time. Conversely, a situation in which you might want to try to increase the likelihood of a table being vacuumed is one in which you have a table that has a high rate of updates, perhaps updating all rows in a matter of minutes. At the time of this writing, the autovacuum feature hasn t quite settled in the code for 8.1, and given that it is a relatively new feature in PostgreSQL, it likely will change somewhat over
Check Tomcat Web Hosting services for best quality webspace to host your web application.