What are the best practices for ANALYZE?

Here are a few tips that will help keep your vacuum locking minimal:

Instead of the vacuumdb command, a script using VACUUM ANALYZE on a table by table basis will give you finer control.

You can break up the VACUUM ANALYZE {tablename} statements to specific tables depending on your user's table usage patterns. So if some tables are used more heavily during the night than other tables, you can vacuum those particular tables during the morning.

You can breakdown the VACUUM ANALYZE's even further. You can vacuum the child tables of a partitioned table instead of vacuuming the entire table in one go.

The more you VACUUM ANALYZE the less time it will take.

Use VACUUM ANALYZE on a table after it has had a bulk data load.

Vacuum activities will require a maintenance window. Daily is best, weekly is good.

The ANALYZE section of VACUUM ANALYZE will update the statistics of a table, allowing the query optimizer to make a more intelligent choice when planning queries. This results in performance increases.

A rule of thumb: The more a table has INSERT,UPDATE, DELETE or bulk load events on it, the more likely that table will need to have the VACUUM ANALYZE command run against it.

The select * from gp_toolkit.gp_bloat_diag; command will show you the tables in most need of a VACUUM.

The select * from gp_toolkit.gp_stats_missing; command will show you the tables in most need of an ANALYZE.

Finally, do not forget to VACUUM ANALYZE your system tables.