Greenplum Analyze the tables

Greenplum uses Multiversion Concurrency Control (MVCC) to guarantee isolation, one of the ACID properties of relational databases. MVCC enables multiple users of the database to obtain consistent results for a query, even if the data is changing as the query is being executed. There can be multiple versions of rows in the database, but a query sees a snapshot of the database at a single point in time, containing only the versions of rows that were valid at that point in time. When a row is updated or deleted and no active transactions continue to reference it, it can be removed. The VACUUM command removes older versions that are no longer needed, leaving free space that can be reused. In a Greenplum database, normal OLTP operations do not create the need for vacuuming out old rows, but loading data while tables are in use may. It is a best practice to VACUUM a table after a load. If the table is partitioned, and only a single partition is being altered, then a VACUUM on that partition may suffice. The VACUUM FULL command behaves much differently than VACUUM, and its use is not recommended in Greenplum databases. It can be expensive in CPU and I/O, cause bloat in indexes, and lock data for long periods of time. The ANALYZE command generates statistics about the distribution of data in a table. In particular it stores histograms about the values in each of the columns. The query optimizer depends on these statistics to select the best plan for executing a query. For example, the optimizer can use distribution data to decide on join orders. One of the optimizer’s goals in a join is to minimize the volume of data that must be analyzed and potentially moved between segments by using the statistics to choose the smallest result set to work with first.

1. Run the ANALYZE command on each of the tables:
   

   $ psql -U gpadmin tutorial
   
   ```
   tutorial=# ANALYZE faa.d_airports;
   ANALYZE
   tutorial=# ANALYZE faa.d_airlines;
   ANALYZE
   tutorial=# ANALYZE faa.d_wac;
   ANALYZE
   tutorial=# ANALYZE faa.d_cancellation_codes;
   ANALYZE
   tutorial=# ANALYZE faa.faa_otp_load;
   ANALYZE
   tutorial=# ANALYZE faa.otp_r;
   ANALYZE
   tutorial=# ANALYZE faa.otp_c;
   ANALYZE
   ```