Greenplum – Check for even data distribution on segments

Check for even data distribution on segments

The faa.otp_r and faa.otp_c tables are distributed with a hash function on UniqueCarrier and FlightNum. These two columns were selected because they produce an even distribution of the data onto the segments. Also, with frequent joins expected on the fact table and dimension tables on these two columns, less data moves between segments, reducing query execution time. When there is no advantage to co-locating data from different tables on the segments, a distribution based on a unique column ensures even distribution. Distributing on a column with low cardinality, such as Diverted, which has only two values, will yield a poor distribution.

1. One of the goals of distribution is to ensure that there is approximately the same amount of data in each segment. The query below shows one way of determining this. Since the column-oriented and row-oriented tables are distributed by the same columns, the counts should be the same for each.
   

   tutorial=# SELECT gp_segment_id, COUNT(*) FROM faa.otp_c GROUP BY
   gp_segment_id ORDER BY gp_segment_id;
   gp_segment_id |  count
   ---------------+---------
            0 | 1028144
            1 | 1020960
   (2 rows)