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).

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

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.

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