Checking Query Disk Spill Space Usage

The gp_workfile_* views show information about all the queries that are currently using disk spill space. Greenplum Database creates work files on disk if it does not have sufficient memory to execute the query in memory. This information can be used for troubleshooting and tuning queries. The information in the views can also be used to specify the values for the Greenplum Database configuration parameters gp_workfile_limit_per_query and gp_workfile_limit_per_segment.

gp_workfile_entries

This view contains one row for each operator using disk space for workfiles on a segment at the current time. The view is accessible to all users, however non-superusers only to see information for the databases that they have permission to access.

Table 1. gp_workfile_entries
Column	Type	Description
`command_cnt`	integer	Command ID of the query.
`content`	smallint	The content identifier for a segment instance.
`current_query`	text	Current query that the process is running.
`datname`	name	Greenplum database name.
`directory`	text	Path to the work file.
`optype`	text	The query operator type that created the work file.
`procpid`	integer	Process ID of the server process.
`sess_id`	integer	Session ID.
`size`	bigint	The size of the work file in bytes.
`numfiles`	bigint	The number of files created.
`slice`	smallint	The query plan slice. The portion of the query plan that is being executed.
`state`	text	The state of the query that created the work file.
`usename`	name	Role name.
`workmem`	integer	The amount of memory allocated to the operator in KB.

gp_workfile_usage_per_query

This view contains one row for each query using disk space for workfiles on a segment at the current time. The view is accessible to all users, however non-superusers only to see information for the databases that they have permission to access.

Table 2. gp_workfile_usage_per_query
Column	Type	Description
`command_cnt`	integer	Command ID of the query.
`content`	smallint	The content identifier for a segment instance.
`current_query`	text	Current query that the process is running.
`datname`	name	Greenplum database name.
`procpid`	integer	Process ID of the server process.
`sess_id`	integer	Session ID.
`size`	bigint	The size of the work file in bytes.
`numfiles`	bigint	The number of files created.
`state`	text	The state of the query that created the work file.
`usename`	name	Role name.

gp_workfile_usage_per_segment

This view contains one row for each segment. Each row displays the total amount of disk space used for workfiles on the segment at the current time. The view is accessible to all users, however non-superusers only to see information for the databases that they have permission to access.

Table 3. gp_workfile_usage_per_segment
Column	Type	Description
`content`	smallint	The content identifier for a segment instance.
`size`	bigint	The total size of the work files on a segment.
`numfiles`	bigint	The number of files created.