Slow performance of first record in tables with many columns

Products

• VMware Tanzu Greenplum

Issue/Introduction

Using CREATE TABLE AS is slow when the table created contains many columns (60 or more columns). Subsequent inserts into the created table are very fast (sub-second).

Inserting the first record into a table with many columns (60 or more columns) is very slow. All subsequent inserts are fast (sub-second).

Resolution

When the first record is inserted into any table, every column is analyzed to create the initial statistics. The time the analysis takes is relative to the number of columns in the table, more columns equal more time. This happens for the first record inserted using INSERT or CREATE TABLE AS.
 

Follow these steps to resolve this issue:
 

1. Set gp_autostats_mode='NONE';

2. Execute SQL to insert records (INSERT or CREATE TABLE AS)

3. Set gp_autostats_mode='ON_NO_STATS';

4. Analyze %CreatedTableName%

Additional Information

• Setting gp_autostats_mode will only affect the current session and when the session ends, the default value will be restored.
• gp_autostats_mode=’NONE’ circumvents creating statistics during the initial insert but statistics need to be created before complex queries are used.
• gp_autostats_mode='ON_NO_STATS' is the default value.