Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

SVV_TABLE_INFO

Shows summary information for tables in the database. The view filters system tables and shows only user-defined tables.

You can use the SVV_TABLE_INFO view to diagnose and address table design issues that can influence query performance, including issues with compression encoding, distribution keys, sort style, data distribution skew, table size, and statistics.

The SVV_TABLE_INFO view summarizes information from the STV_BLOCKLIST, STV_PARTITIONS, STV_TBL_PERM, and STV_SLICES system tables and from the PG_DATABASE, PG_ATTRIBUTE, PG_CLASS, PG_NAMESPACE, and PG_TYPE catalog tables.

SVV_TABLE_INFO is visible only to superusers. For more information, see Visibility of Data in System Tables and Views. To permit a user to query the view, grant SELECT privilege on SVV_TABLE_INFO to the user.

Table Columns

Column Name Data Type Description
database text Database name.
schema text Schema name.
table_id oid Table ID.
table text Table name.
encoded text Value that indicates whether any column has compression encoding defined.
diststyle text Distribution style or distribution key column, if key distribution is defined.
sortkey1 text First column in the sort key, if a sort key is defined.
max_varchar integer Size of the largest column that uses a VARCHAR data type.
sortkey1_enc character(32) Compression encoding of the first column in the sort key, if a sort key is defined.
sortkey_num integer Number of columns defined as sort keys.
size bigint Size of the table, in 1 MB data blocks.
pct_used numeric(10,4) Percent of available space that is used by the table.
empty bigint For internal use. This column is deprecated and will be removed in a future release.
unsorted numeric(5,2) Percent of unsorted rows in the table.
stats_off numeric(5,2) Number that indicates how stale the table's statistics are; 0 is current, 100 is out of date.
tbl_rows numeric(38,0) Total number of rows in the table. This values includes rows marked for deletion, but not yet vacuumed.
skew_sortkey1 numeric(19,2) Ratio of the size of the largest non-sort key column to the size of the first column of the sort key, if a sort key is defined. Use this value to evaluate the effectiveness of the sort key.
skew_rows numeric(19,2) Ratio of the number of rows in the slice with the most rows to the number of rows in the slice with the fewest rows.

Sample Queries

The following example shows encoding, distribution style, sorting, and data skew for all user-defined tables in the database. Note that "table" must be enclosed in double quotes because it is a reserved word.

Copy
select "table", encoded, diststyle, sortkey1, skew_sortkey1, skew_rows from svv_table_info order by 1; table | encoded | diststyle | sortkey1 | skew_sortkey1 | skew_rows ---------------+---------+-----------------+--------------+---------------+---------- category | N | EVEN | | | date | N | ALL | dateid | 1.00 | event | Y | KEY(eventid) | dateid | 1.00 | 1.02 listing | Y | KEY(listid) | dateid | 1.00 | 1.01 sales | Y | KEY(listid) | dateid | 1.00 | 1.02 users | Y | KEY(userid) | userid | 1.00 | 1.01 venue | N | ALL | venueid | 1.00 | (7 rows)