Greenplum Best Practises

Best Practices:

1. A distribution key should not have more than 2 columns, recommended is 1 column.
2. While modeling a database, we need to make sure all the tables that gets involved in longer query execution are distributed using the joining column.
3. Distribution keys should be domain key but not surrogate keys.
4. Explicitly define a column or random distribution for all tables. Do not use the default as it may cause skew on segments.
5. Do not distribute on columns that will be used in the WHERE clause of a query. You should partition the data on the column that is used in WHERE clause
6. The distribution key column data should contain unique values
7. Do not distribute on dates or timestamps. Distributing data on date or timestamp may cause poor system performance
8. There are 2 questions to ask while deciding the distribution key of a table:
1. Is the Distribution Key used in joins?
1. Makes sure, no redistribution is happening during query execution
2. Does the key 'Equi-distributes'  the data across all the segments?
1. Makes sure all the segments get approximately the same amount of data, hence the same amount of work during query execution.
2. We need to make sure data skewing is minimum for every table.

SELECT * FROM gp_toolkit.gp_skew_coefficients