我的书签
添加书签
移除书签The hawq_toolkit Administrative Schema
HAWQ provides an administrative schema called hawq_toolkit that you can use to query the system catalogs, log files, and operating environment for system status information. The hawq_toolkit schema contains a number of views that you can access using SQL commands. The hawq_toolkit schema is accessible to all database users, although some objects may require superuser permissions.
This documentation describes the most useful views in hawq_toolkit. You may notice other objects (views, functions, and external tables) within the schema that are not described in this documentation (these are supporting objects to the views described in this section).
Warning: Do not change database objects in the hawq_toolkit schema. Do not create database objects in the schema. Changes to objects in the schema might affect the accuracy of administrative information returned by schema objects.
The following views can help identify tables that need routine table maintenance (VACUUM and/or ANALYZE).
The VACUUM command is applicable only to system catalog tables. The command reclaims disk space occupied by deleted or obsolete rows. Because of the MVCC transaction concurrency model used in HAWQ, data rows that are deleted or updated still occupy physical space on disk even though they are not visible to any new transactions. Expired rows increase table size on disk and eventually slow down scans of the table.
Note: VACUUM FULL is not recommended in HAWQ. See .
The ANALYZE command collects column-level statistics needed by the query optimizer. HAWQ uses a cost-based query optimizer that relies on database statistics. Accurate statistics allow the query optimizer to better estimate selectivity and the number of rows retrieved by a query operation in order to choose the most efficient query plan.
This view shows tables that do not have statistics and therefore may require an ANALYZE be run on the table.
Table 1. hawq_stats_missing view
Each component of a HAWQ system (master, standby master, and segments) keeps its own server log files. The hawq_log_* family of views allows you to issue SQL queries against the server log files to find particular entries of interest. The use of these views requires superuser permissions.
hawq_log_command_timings
Table 2. hawq_log_command_timings view
| Column | Description |
|---|---|
| logsession | The session identifier (prefixed with “con”). |
| logcmdcount | The command number within a session (prefixed with “cmd”). |
| logdatabase | The name of the database. |
| loguser | The name of the database user. |
| logpid | The process id (prefixed with “p”). |
| logtimemin | The time of the first log message for this command. |
| logtimemax | The time of the last log message for this command. |
| logduration | Statement duration from start to end time. |
hawq_log_master_concise
This view uses an external table to read a subset of the log fields from the master log file. The use of this view requires superuser permissions.
Table 3. hawq_log_master_concise view
| Column | Description |
|---|---|
| logtime | The timestamp of the log message. |
| logdatabase | The name of the database. |
| logsession | The session identifier (prefixed with “con”). |
| logcmdcount | The command number within a session (prefixed with “cmd”). |
| logseverity | The severity level for the record. |
| logmessage | Log or error message text. |
The hawq_size_* family of views can be used to determine the disk space usage for a distributed HAWQ, schema, table, or index. The following views calculate the total size of an object across all segments.
- hawq_size_of_database
- hawq_size_of_partition_and_indexes_disk
- hawq_size_of_table_and_indexes_disk
- hawq_size_of_table_disk
The table and index sizing views list the relation by object ID (not by name). To check the size of a table or index by name, you must look up the relation name () in the pg_class table. For example:
hawq_size_of_all_table_indexes
This view shows the total size of all indexes for a table. This view is accessible to all users, however non-superusers will only be able to see relations that they have permission to access.
Table 4. hawq_size_of_all_table_indexes view
| Column | Description |
|---|---|
| soatioid | The object ID of the table |
| soatisize | The total size of all table indexes in bytes |
| soatischemaname | The schema name |
| soatitablename | The table name |
This view shows the total size of a database. This view is accessible to all users, however non-superusers will only be able to see databases that they have permission to access.
Table 5. hawq_size_of_database view
hawq_size_of_index
This view shows the total size of an index. This view is accessible to all users, however non-superusers will only be able to see relations that they have permission to access.
| Column | Description |
|---|---|
| soioid | The object ID of the index |
| soitableoid | The object ID of the table to which the index belongs |
| soisize | The size of the index in bytes |
| soiindexschemaname | The name of the index schema |
| soiindexname | The name of the index |
| soitableschemaname | The name of the table schema |
| soitablename | The name of the table |
hawq_size_of_partition_and_indexes_disk
This view shows the size on disk of partitioned child tables and their indexes. This view is accessible to all users, however non-superusers will only be able to see relations that they have permission to access.
Table 7. hawq_size_of_partition_and_indexes_disk view
| Column | Description |
|---|---|
| sopaidparentoid | The object ID of the parent table |
| sopaidpartitionoid | The object ID of the partition table |
| sopaidpartitiontablesize | The partition table size in bytes |
| sopaidpartitionindexessize | The total size of all indexes on this partition |
| Sopaidparentschemaname | The name of the parent schema |
| Sopaidparenttablename | The name of the parent table |
| Sopaidpartitionschemaname | The name of the partition schema |
| sopaidpartitiontablename | The name of the partition table |
hawq_size_of_schema_disk
This view shows schema sizes for the public schema and the user-created schemas in the current database. This view is accessible to all users, however non-superusers will be able to see only the schemas that they have permission to access.
Table 8. hawq_size_of_schema_disk view
| Column | Description |
|---|---|
| sosdnsp | The name of the schema |
| sosdschematablesize | The total size of tables in the schema in bytes |
| sosdschemaidxsize | The total size of indexes in the schema in bytes |
This view shows the size on disk of tables and their indexes. This view is accessible to all users, however non-superusers will only be able to see relations that they have permission to access.
Table 9. hawq_size_of_table_and_indexes_disk view
hawq_size_of_table_and_indexes_licensing
This view shows the total size of tables and their indexes for licensing purposes. The use of this view requires superuser permissions.
Table 10. hawq_size_of_table_and_indexes_licensing view
| Column | Description |
|---|---|
| sotailoid | The object ID of the table |
| sotailtablesizedisk | The total disk size of the table |
| sotailtablesizeuncompressed | If the table is a compressed append-only table, shows the uncompressed table size in bytes. |
| sotailindexessize | The total size of all indexes in the table |
| sotailschemaname | The schema name |
| sotailtablename | The table name |
hawq_size_of_table_disk
This view shows the size of a table on disk. This view is accessible to all users, however non-superusers will only be able to see tables that they have permission to access
Table 11. hawq_size_of_table_disk view
| Column | Description |
|---|---|
| sotdoid | The object ID of the table |
| sotdsize | The size of the table in bytes. The size is only the main table size. The size does not include auxiliary objects such as oversized (toast) attributes, or additional storage objects for AO tables. |
| sotdtoastsize | The size of the TOAST table (oversized attribute storage), if there is one. |
| sotdadditionalsize | Reflects the segment and block directory table sizes for append-only (AO) tables. |
| sotdschemaname | The schema name |
| sotdtablename | The table name |
hawq_size_of_table_uncompressed
Table 12. hawq_size_of_table_uncompressed view
| Column | Description |
|---|---|
| sotuoid | The object ID of the table |
| sotusize | The uncomressed size of the table in bytes if it is a compressed AO table. Otherwise, the table size on disk. |
| sotuschemaname | The schema name |
| sotutablename | The table name |
