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 doesn't return any information for empty tables.
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
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. Possible values include
EVEN,
KEY(column),
ALL,
AUTO(ALL),
AUTO(EVEN), and
AUTO(KEY(column)).
|
| sortkey1 | text | First column in the sort key, if a sort key is
defined. Possible values include
column,
AUTO(SORTKEY), and
AUTO(SORTKEY(column)).
|
| 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 value 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. |
| estimated_visible_rows | numeric(38,0) | The estimated rows in the table. This value does not include rows marked for deletion. |
| risk_event | text | Risk information about a table. The field is separated into parts:
The following example shows the values in the field.
|
| vacuum_sort_benefit | numeric(12,2) | The estimated maximum percentage improvement of scan query performance when you run vacuum sort. |
Sample queries
The following example shows encoding, distribution style, sorting, and data skew for all user-defined tables in the database. Here, "table" must be enclosed in double quotation marks because it is a reserved word.
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)
