The hawq_toolkit Administrative Schema

The hawq_toolkit Administrative Schema

This section provides a reference on 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 hawq_toolkit 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.

Checking for Tables that Need Routine Maintenance

The following views can help identify tables that need routine table maintenance (VACUUM and/or ANALYZE).

hawq_stats_missing

The VACUUM command is applicable only to system catalog tables. The VACUUM 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 VACUUM.

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.

hawq_stats_missing

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

Column	Description
smischema	Schema name.
smitable	Table name.
smisize	Does this table have statistics? False if the table does not have row count and row sizing statistics recorded in the system catalog, which may indicate that the table needs to be analyzed. This will also be false if the table does not contain any rows. For example, the parent tables of partitioned tables are always empty and will always return a false result.
smicols	Number of columns in the table.
smirecs	Number of rows in the table.

Viewing HAWQ Server Log Files

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
hawq_log_master_concise

hawq_log_command_timings

This view uses an external table to read the log files on the master and report the execution time of SQL commands executed in a database session. The use of this view requires superuser permissions.

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.

Checking Database Object Sizes and Disk Space

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_all_table_indexes
hawq_size_of_database
hawq_size_of_index
hawq_size_of_partition_and_indexes_disk
hawq_size_of_schema_disk
hawq_size_of_table_and_indexes_disk
hawq_size_of_table_and_indexes_licensing
hawq_size_of_table_disk
hawq_size_of_table_uncompressed

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 (relname) in the pg_class table. For example:

SELECT relname AS name, sotdsize AS size, sotdtoastsize AS 
toast, sotdadditionalsize AS other 
FROM hawq_size_of_table_disk AS sotd, pg_class 
WHERE sotd.sotdoid=pg_class.oid ORDER BY relname;

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

hawq_size_of_database

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

Column	Description
sodddatname	The name of the database
sodddatsize	The size of the database in bytes

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.

Table 6. hawq_size_of_index view

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

hawq_size_of_table_and_indexes_disk

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

Column	Description
sotaidoid	The object ID of the parent table
sotaidtablesize	The disk size of the table
sotaididxsize	The total size of all indexes on the table
sotaidschemaname	The name of the schema
sotaidtablename	The name of the table

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

This view shows the uncompressed table size for append-only (AO) tables. Otherwise, the table size on disk is shown. The use of this view requires superuser permissions.

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