Statistics in PostgreSQL databases

PostgreSQL documentation recommends that most sites run the ANALYZE command to update database statistics on a daily basis when few or no users are accessing the database. You can run the PostgreSQL ANALYZE command by itself or along with the VACUUM command. Since you should also run the PostgreSQL VACUUM command on a regular basis to recover disk space, you might want to include an ANALYZE command with the VACUUM command.

You can script this to run nightly, when it is likely that fewer users would be connecting to the database. There is also an autovacuum option available. Consult the PostgreSQL documentation at http://www.postgresql.org/docs for details on the ANALYZE command, VACUUM command, and autovacuum option.

You can also update statistics on individual tables using the sdetable –o update_statistics command. The syntax for this command is as follows:

sdetable -o update_dbms_stats &t <table_name> [–K <keyword>]
[–m {VACUUM | VACUUM_FULL}] [–n {ALL | <index_name>}]
[–i <service>] [–s <server_name>] [–D <database>]
–u <DB_user_name> [–p <DB_user_password>] [–N] [–q]

For details on running the sdetable command, consult the ArcSDE Administration Command Reference provided with ArcGIS Server Enterprise.

You can also use the Analyze command in the Catalog window or the Analyze geoprocessing tool to update statistics on individual tables. See Updating statistics on a geodatabase using Analyze and Analyze for information on each of these methods.

Related Topics


11/18/2013