Pivotal Knowledge Base

Follow

Slow Performance of First Record in Tables with Many Columns

Environment

Pivotal Greenplum Database (GPDB) all versions

Introduction

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

Description

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:

  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.

Comments

Powered by Zendesk