Schemas:

ACCOUNT_USAGE

SEARCH_OPTIMIZATION_BENEFITS View

This Account Usage view can be used to determine the efficacy of pruning due to search optimization. You can use this view to compare the effects on pruning before and after adding search optimization to a table.

To determine the efficacy of pruning due to search optimization, you can compare the number of partitions pruned due to search optimization (PARTITIONS_PRUNED_ADDITIONAL) against the total number of partitions pruned (PARTITIONS_PRUNED_DEFAULT + PARTITIONS_PRUNED_ADDITIONAL).

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 on the table during the START_TIME and END_TIME window) that used search optimization to improve pruning. 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_DEFAULT

NUMBER

Number of partitions that were pruned as a result of the default (natural) ordering of data during the scan operations described in NUM_SCANS.

PARTITIONS_PRUNED_ADDITIONAL

NUMBER

Number of partitions that were pruned as a result of search optimization during the scan operations described in NUM_SCANS.

Usage Notes

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

Examples

List the tables that have benefited the most from search optimization within the last 24 hours:

SELECT * FROM snowflake.account_usage.search_optimization_benefits
WHERE start_time > DATEADD(hour, -24, CURRENT_TIMESTAMP())
ORDER BY partitions_pruned_additional / GREATEST(partitions_pruned_default + partitions_pruned_additional, 1) DESC,
partitions_pruned_additional DESC;
Copy

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

Language: English