PARTITION_COLUMNS

For each projection of a partitioned table, shows the following information:.

For each projection of a partitioned table, shows the following information:

  • Disk space used by each column per node.

  • Statistics that were collected on partition columns

Disk usage

The column DISK_SPACE_BYTES shows how much disk space the partitioned data uses, including deleted data. So, if you delete rows but do not purge them, the DELETED_ROW_COUNT column changes to show the number of deleted rows in each column; however, DISK_SPACE_BYTES remains unchanged. After deleted rows are purged, Vertica,reclaims the disk space: DISK_SPACE_BYTES changes accordingly, and DELETED_ROW_COUNT is reset to 0.

For grouped partitions, PARTITION_COLUMNS shows the cumulative disk space used for each column per grouped partition. The column GROUPED_PARTITION_KEY, if not null, identifies the partition in which a given column is grouped.

Statistics

STATISTICS_TYPE always shows the most complete type of statistics that are available on a given column, irrespective of timestamp. For example, if you collect statistics for a table on all levels—table, partition, and row, STATISTICS_TYPE is set to FULL (table-level), even if partition- and row-level statistics were collected more recently.

Column Name Data Type Description
COLUMN_NAME VARCHAR Identifies a named column within the partitioned table.
COLUMN_ID INTEGER Unique numeric ID assigned by the Vertica, which identifies the column.
TABLE_NAME VARCHAR Name of the partitioned table.
PROJECTION_NAME VARCHAR Projection name for which information is listed.
PROJECTION_ID INTEGER Unique numeric ID assigned by Vertica, which identifies the projection.
NODE_NAME VARCHAR Node that hosts partitioned data.
PARTITION_KEY VARCHAR Identifies the table partition.
GROUPED_PARTITION_KEY VARCHAR Identifies the grouped partition to which a given column belongs.
ROW_COUNT INTEGER The total number of partitioned data rows for each column, including deleted rows.
DELETED_ROW_COUNT INTEGER Number of deleted partitioned data rows in each column.
DISK_SPACE_BYTES INTEGER Amount of space used by partitioned data.
STATISTICS_TYPE VARCHAR

Specifies what sort of statistics are used for this column, one of the following listed in order of precedence:

  1. FULL: Table-level statistics

  2. PARTITION: Partition-level statistics

  3. ROWCOUNT: Minimal set of statistics and aggregate row counts

STATISTICS_UPDATED_TIMESTAMP TIMESTAMPTZ Specifies when statistics of the type specified in STATISTICS_TYPE were collected for this column.

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

Examples

Given the following table definition:

=> CREATE TABLE messages
(
    time_interval timestamp NOT NULL,
    thread_id varchar(32) NOT NULL,
    unique_id varchar(53) NOT NULL,
    msg_id varchar(65),
    ...
    )
PARTITION BY ((messages.time_interval)::date);

a query on partition_columns might return the following (truncated) results:

=> SELECT * FROM partition_columns order by table_name, column_name;
column_name | column_id | table_name | projection_name | projection_id     | node_name        | partition_key | grouped_partition_key  | row_count | deleted_row_count | disk_space_bytes
---------------------------+----------+----------------+-------------------+------------------+---------------+------------------------+-----------+-------------------+------------------
msg_id | 45035996273743190 | messages | messages_super | 45035996273743182 | v_vmart_node0002 | 2010-07-03    |                        | 6147      | 0                 | 41145
msg_id | 45035996273743190 | messages | messages_super | 45035996273743182 | v_vmart_node0002 | 2010-07-15    |                        | 178       | 0                 | 65
msg_id | 45035996273743190 | messages | messages_super | 45035996273743182 | v_vmart_node0003 | 2010-07-03    |                        | 6782      | 0                 | 45107
msg_id | 45035996273743190 | messages | messages_super | 45035996273743182 | v_vmart_node0003 | 2010-07-04    |                        | 866       | 0                 | 5883
...

thread_id | 45035996273743186 | messages | messages_super | 45035996273743182 | v_vmart_node0002 | 2010-07-03 |                        | 6147      | 0                 | 70565
thread_id | 45035996273743186 | messages | messages_super | 45035996273743182 | v_vmart_node0002 | 2010-07-15 |                        | 178       | 0                 | 2429
thread_id | 45035996273743186 | messages | messages_super | 45035996273743182 | v_vmart_node0003 | 2010-07-03 |                        | 6782      | 0                 | 77730
thread_id | 45035996273743186 | messages | messages_super | 45035996273743182 | v_vmart_node0003 | 2010-07-04 |                        | 866       | 0                 | 10317
...

time_interval | 45035996273743184 | messages | messages_super | 45035996273743182 | v_vmart_node0002 | 2010-07-03 |                    | 6147      | 0                 | 6320
time_interval | 45035996273743184 | messages | messages_super | 45035996273743182 | v_vmart_node0002 | 2010-07-15 |                    | 178       | 0                 | 265
time_interval | 45035996273743184 | messages | messages_super | 45035996273743182 | v_vmart_node0003 | 2010-07-03 |                    | 6782      | 0                 | 6967
time_interval | 45035996273743184 | messages | messages_super | 45035996273743182 | v_vmart_node0003 | 2010-07-04 |                    | 866       | 0                 | 892
...

unique_id | 45035996273743188 | messages | messages_super | 45035996273743182 | v_vmart_node0002 | 2010-07-03 |                        | 6147      | 0                 | 70747
unique_id | 45035996273743188 | messages | messages_super | 45035996273743182 | v_vmart_node0002 | 2010-07-15 |                        | 178       | 0                 | 2460
unique_id | 45035996273743188 | messages | messages_super | 45035996273743182 | v_vmart_node0003 | 2010-07-03 |                        | 6782      | 0                 | 77959
unique_id | 45035996273743188 | messages | messages_super | 45035996273743182 | v_vmart_node0003 | 2010-07-04 |                        | 866       | 0                 | 10332
unique_id | 45035996273743188 | messages | messages_super | 45035996273743182 | v_vmart_node0003 | 2010-07-15 |                        | 184       | 0                 | 2549
...

(11747 rows)