Schemas:

ACCOUNT_USAGE

TABLE_PRUNING_HISTORY view

This Account Usage view can be used to determine the efficiency of pruning for all tables, and to understand how a table’s default (natural) ordering of data affects pruning.

You can compare the number of partitions pruned (PARTITIONS_PRUNED) to the total number of partitions scanned and pruned (PARTITIONS_SCANNED + PARTITIONS_PRUNED).

You can also use this view to compare the effects on pruning before and after enabling Automatic Clustering and search optimization for a table.

Columns

Column Name

Data Type

Description

START_TIME

TIMESTAMP_LTZ

Start of the time range (on the hour mark) during which the queries were executed.

END_TIME

TIMESTAMP_LTZ

End of the time range (on the hour mark) during which the queries were executed.

TABLE_ID

NUMBER

Internal/system-generated identifier for the table that was queried.

TABLE_NAME

TEXT

Name of the table that was queried.

SCHEMA_ID

NUMBER

Internal/system-generated identifier for the schema that contains the table that was queried.

SCHEMA_NAME

TEXT

Name of the schema that contains the table that was queried.

DATABASE_ID

NUMBER

Internal/system-generated identifier for the database that contains the table that was queried.

DATABASE_NAME

TEXT

Name of the database that contains the table that was queried.

NUM_SCANS

NUMBER

Number of scan operations from all queries (including SELECT statements and DML statements) on the table during the START_TIME and END_TIME window. Note that a given query might result in multiple scan operations on the same table.

PARTITIONS_SCANNED

NUMBER

Number of partitions scanned during the scan operations described in NUM_SCANS.

PARTITIONS_PRUNED

NUMBER

Number of partitions pruned during the scan operations described in NUM_SCANS.

ROWS_SCANNED

NUMBER

Number of rows scanned during the scan operations described in NUM_SCANS.

ROWS_PRUNED

NUMBER

Number of rows pruned during the scan operations described in NUM_SCANS.

Usage notes

  • Latency for the view may be up to 6 hours.

  • This view does not include pruning information for hybrid tables.

Examples

List the top five tables that had the worst pruning efficiency within the last seven days:

SELECT
    table_id,
    ANY_VALUE(table_name) AS table_name,
    SUM(num_scans) AS total_num_scans,
    SUM(partitions_scanned) AS total_partitions_scanned,
    SUM(partitions_pruned) AS total_partitions_pruned,
    SUM(rows_scanned) AS total_rows_scanned,
    SUM(rows_pruned) AS total_rows_pruned
  FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_PRUNING_HISTORY
  WHERE start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP())
  GROUP BY table_id
  ORDER BY
    total_partitions_pruned / GREATEST(total_partitions_scanned + total_partitions_pruned, 1),
    total_partitions_scanned DESC
  LIMIT 5;
Copy
+----------+----------------+-----------------+--------------------------+-------------------------+--------------------+-------------------+
| TABLE_ID | TABLE_NAME     | TOTAL_NUM_SCANS | TOTAL_PARTITIONS_SCANNED | TOTAL_PARTITIONS_PRUNED | TOTAL_ROWS_SCANNED | TOTAL_ROWS_PRUNED |
|----------+----------------+-----------------+--------------------------+-------------------------+--------------------+-------------------|
|   308226 | SENSOR_DATA_TS |              11 |                       21 |                       1 |           52500000 |           2500000 |
|   185364 | MATCH          |              16 |                       14 |                       2 |             240968 |             34424 |
|   209932 | ORDER_HEADER   |               2 |                      300 |                      56 |          421051748 |          75350790 |
|   209922 | K7_T1          |             261 |                      261 |                      52 |              30421 |              3272 |
|   338948 | SENSOR_DATA_TS |               9 |                       15 |                       3 |           38880000 |           8035200 |
+----------+----------------+-----------------+--------------------------+-------------------------+--------------------+-------------------+

The example above uses GREATEST to avoid dividing by zero when the sum of the number of partitions scanned and the number of partitions pruned is zero.

Language: English