iterators_*

The iterators_* tables store information about query plan iterators and their metrics. A query iterator refers to a node or operation in a query plan. For example, a sequential scan operation on a table may be one type of iterator in a particular query plan.

The tmid, ssid and ccnt columns are the composite key that uniquely identifies a particular query. These columns can be used to join with the queries_* data tables.

There are three iterator tables, all having the same columns:

  • iterators_now is an external table whose data files are stored in $MASTER_DATA_DIRECTORY/gpperfmon/data. Current query plan iterator data is stored in iterators_now during the period between data collection from the Command Center agents and automatic commitment to the iterators_history table.
  • iterators_tail is an external table whose data files are stored in $MASTER_DATA_DIRECTORY/gpperfmon/data. This is a transitional table for query plan iterator data that has been cleared from iterators_now but has not yet been committed to iterators_history. It typically only contains a few minutes worth of data.
  • iterators_history is a regular table that stores historical query plan iterator data. It is pre-partitioned into monthly partitions. Partitions are automatically added in two month increments as needed. Administrators must drop old partitions for the months that are no longer needed.

See also the iterator_rollup views for summary metrics of the query plan iterator data.

Column Type Description
ctime timestamp Time this row was created.
tmid int A time identifier for a particular query. All iterator records associated with the query will have the same tmid.
ssid int The session id as shown by the gp_session_id parameter. All iterator records associated with the query will have the same ssid.
ccnt int The command number within this session as shown by gp_command_count parameter. All iterator records associated with the query will have the same ccnt.
segid int The segment ID (dbid from gp_segment_configuration).
pid int The postgres process ID for this iterator.
nid int

The query plan node ID from the Greenplum slice plan.
pnid int The parent query plan node ID from the Greenplum slice plan.
hostname varchar(64)

Segment hostname.
ntype varchar(64)

The iterator operation type. Possible values are listed in [Iterator Metrics](db-iterator-metrics.html).
nstatus varchar(64) The status of this iterator. Possible values are: Initialize, Executing and Finished.
tstart timestamp Start time for the iterator.
tduration int Duration of the execution.
pmemsize bigint Maximum work memory allocated by the Greenplum planner to this iterator’s query process.
memsize bigint OS memory allocated to this iterator’s process.
memresid bigint Resident memory allocated to this iterator’s process (as opposed to shared memory).
memshare bigint Shared memory allocated to this iterator’s process.
cpu_elapsed bigint Total CPU usage of the process executing the iterator.
cpu_currpct float

The percentage of CPU currently being utilized by this iterator process. This value is always zero for historical (completed) iterators.
rowsout bigint The actual number of rows output by the iterator.
rowsout_est bigint The query planner’s estimate of rows output by the iterator.
m0_name varchar(64) Each operation in a query plan (ntype) has metrics associated with it. For all operations, this metric name is Rows In.
m0_unit varchar(64) The unit of measure for this metric. For all operations (ntype), this unit of measure is Rows.
m0_val bigint The value of this metric.
m0_est bigint The estimated value of this metric.
m1_name varchar(64) Each operation in a query plan (ntype) has metrics associated with it. See [Iterator Metrics](db-iterator-metrics.html) for a complete list of iterator attributes and their corresponding units.
m1_unit varchar(64) The unit of measure for this metric. See [Iterator Metrics](db-iterator-metrics.html) for a complete list of iterator attributes and their corresponding units.
m1_val bigint The value of this metric.
m1_est bigint The estimated value of this metric.
m2_name varchar(64) Each operation in a query plan (ntype) has metrics associated with it. See [Iterator Metrics](db-iterator-metrics.html) for a complete list of iterator attributes and their corresponding units.
m2_unit varchar(64) The unit of measure for this metric. See [Iterator Metrics](db-iterator-metrics.html) for a complete list of iterator attributes and their corresponding units.
m2_val bigint The value of this metric.
m2_est bigint The estimated value of this metric.
m3_name varchar(64) Each operation in a query plan (ntype) has metrics associated with it. See [Iterator Metrics](db-iterator-metrics.html) for a complete list of iterator attributes and their corresponding units.
m3_unit varchar(64) The unit of measure for this metric. See [Iterator Metrics](db-iterator-metrics.html) for a complete list of iterator attributes and their corresponding units.
m3_val bigint The value of this metric.
m3_est bigint The estimated value of this metric.
m4_name varchar(64) Each operation in a query plan (ntype) has metrics associated with it. See [Iterator Metrics](db-iterator-metrics.html) for a complete list of iterator attributes and their corresponding units.
m4_unit varchar(64) The unit of measure for this metric. See [Iterator Metrics](db-iterator-metrics.html) for a complete list of iterator attributes and their corresponding units.
m4_val bigint The value of this metric.
m4_est bigint The estimated value of this metric.
m5_name varchar(64) Each operation in a query plan (ntype) has metrics associated with it. See [Iterator Metrics](db-iterator-metrics.html) for a complete list of iterator attributes and their corresponding units.
m5_unit varchar(64) The unit of measure for this metric. See [Iterator Metrics](db-iterator-metrics.html) for a complete list of iterator attributes and their corresponding units.
m5_val bigint The value of this metric.
m5_est bigint The estimated value of this metric.
m6_name varchar(64) Each operation in a query plan (ntype) has metrics associated with it. See [Iterator Metrics](db-iterator-metrics.html) for a complete list of iterator attributes and their corresponding units.
m6_unit varchar(64) The unit of measure for this metric. See [Iterator Metrics](db-iterator-metrics.html) for a complete list of iterator attributes and their corresponding units.
m6_val bigint The value of this metric.
m6_est bigint The estimated value of this metric.
m7_name varchar(64) Each operation in a query plan (ntype) has metrics associated with it. See [Iterator Metrics](db-iterator-metrics.html) for a complete list of iterator attributes and their corresponding units.
m7_unit varchar(64) The unit of measure for this metric. See [Iterator Metrics](db-iterator-metrics.html) for a complete list of iterator attributes and their corresponding units.
m7_val bigint The value of this metric.
m7_est bigint The estimated value of this metric.
m8_name varchar(64) Each operation in a query plan (ntype) has metrics associated with it. See [Iterator Metrics](db-iterator-metrics.html) for a complete list of iterator attributes and their corresponding units.
m8_unit varchar(64) The unit of measure for this metric. See [Iterator Metrics](db-iterator-metrics.html) for a complete list of iterator attributes and their corresponding units.
m8_val bigint The actual value of this metric.
m8_est bigint The estimated value of this metric.
m9_name varchar(64) Each operation in a query plan (ntype) has metrics associated with it. See [Iterator Metrics](db-iterator-metrics.html) for a complete list of iterator attributes and their corresponding units.
m9_unit varchar(64) The unit of measure for this metric. See [Iterator Metrics](db-iterator-metrics.html) for a complete list of iterator attributes and their corresponding units.
m9_val bigint The actual value of this metric.
m9_est bigint The estimated value of this metric.
m10_name - m15_name varchar(64) The iterator name (ntype) associated with this metric. Metrics m10 through m15 are currently not used.
m10_unit - m15_unit varchar(64) The unit of measure for this metric. Metrics m10 throughm15 are currently not used.
m10_value - m15_value bigint The actual value of this metric. Metrics m10 through m15 are currently not used.
m10_est - m15_est bigint The estimated value of this metric. Metrics m10 through m15 are currently not used.
t0_name varchar(64) This column is a label for t0_val. Its value is always Name.
t0_val varchar(128) The name of the relation being scanned by an iterator. This metric is collected only for iterators that perform scan operations such as a sequential scan or function scan.