SYS_ANALYZE_COMPRESSION_HISTORY - Amazon Redshift
Table columnsSample queries

SYS_ANALYZE_COMPRESSION_HISTORY

Records details for compression analysis operations during COPY or ANALYZE COMPRESSION commands.

SYS_ANALYZE_COMPRESSION_HISTORY is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see Visibility of data in system tables and views.

Table columns

Column name Data type Description
user_id integer The ID of the user who generated the entry.
start_time timestamp The time when the compression analysis operation started.
transaction_id bigint The transaction ID of the compression analysis operation.
table_id integer The table ID of the table that was analyzed.
table_name character(128) The name of the table that was analyzed.
column_position integer The index of the column in the table that was analyzed to determine the compression encoding.
old_encoding character(15) The encoding type before compression analysis.
new_encoding character(15) The encoding type after compression analysis.
mode character(14)

The possible values are:

PRESET

Specifies that the new_encoding is determined by the Amazon Redshift COPY command based on the column data type. No data is sampled.

ON

Specifies that the new_encoding is determined by the Amazon Redshift COPY command based on an analysis of sample data.

ANALYZE ONLY

Specifies that the new_encoding is determined by the Amazon Redshift ANALYZE COMPRESSION command based on an analysis of sample data. However, the encoding type of the analyzed column is not changed.

Sample queries

The following example inspects the details of compression analysis on the lineitem table by the last COPY command run in the same session. 

select transaction_id, table_id, btrim(table_name) as table_name, column_position, old_encoding, new_encoding, mode 
from sys_analyze_compression_history
where transaction_id = (select transaction_id from sys_query_history where query_id = pg_last_copy_id()) order by column_position;
                
 transaction_id  |  table_id   | table_name | column_position |  old_encoding   |  new_encoding   |      mode
-----------------+-------------+------------+-----------------+-----------------+-----------------+-------------
      8196       |   248126    | lineitem   |        0        | mostly32        | mostly32        | ON
      8196       |   248126    | lineitem   |        1        | mostly32        | lzo             | ON
      8196       |   248126    | lineitem   |        2        | lzo             | delta32k        | ON
      8196       |   248126    | lineitem   |        3        | delta           | delta           | ON
      8196       |   248126    | lineitem   |        4        | bytedict        | bytedict        | ON
      8196       |   248126    | lineitem   |        5        | mostly32        | mostly32        | ON
      8196       |   248126    | lineitem   |        6        | delta           | delta           | ON
      8196       |   248126    | lineitem   |        7        | delta           | delta           | ON
      8196       |   248126    | lineitem   |        8        | lzo             | zstd            | ON
      8196       |   248126    | lineitem   |        9        | runlength       | zstd            | ON
      8196       |   248126    | lineitem   |       10        | delta           | lzo             | ON
      8196       |   248126    | lineitem   |       11        | delta           | delta           | ON
      8196       |   248126    | lineitem   |       12        | delta           | delta           | ON
      8196       |   248126    | lineitem   |       13        | bytedict        | zstd            | ON
      8196       |   248126    | lineitem   |       14        | bytedict        | zstd            | ON
      8196       |   248126    | lineitem   |       15        | text255         | zstd            | ON
(16 rows)