Pivotal Knowledge Base

Follow

Slow Performance of first record in tables with many columns

Environment

Product Version
Pivotal Greenplum All Versions

Problem

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).

 OR

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

Cause

 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 equals more time. This happens for the first record inserted using INSERT or CREATE TABLE AS.

Solution

  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%

Notes

  • Setting gp_autostats_mode will only affect the current session and when the session ends the default value we 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.

Comments

Powered by Zendesk