Pivotal Greenplum Database (GPDB) all versions
Using CREATE TABLE AS is slow when the table created contains many columns (60+). Subsequent inserts into the created table are very fast (sub-second).
Inserting the first record into a table with many columns (60+) is very slow. All subsequent inserts are fast (sub-second).
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:
- set gp_autostats_mode='NONE';
- Execute SQL to insert records (INSERT or CREATE TABLE AS)
- set gp_autostats_mode='ON_NO_STATS';
- analyze %CreatedTableName%
- 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.