src/queries/network_policy_changes/network_policy_changes.sql doing full table scan

[sfc-gh-jpfeiffer]

select user_name || ' made the following Network Policy change on ' || end_time || ' [' || query_text || ']' as Events
from SNOWFLAKE.ACCOUNT_USAGE.query_history where execution_status = 'SUCCESS'
and query_type in ('CREATE_NETWORK_POLICY', 'ALTER_NETWORK_POLICY', 'DROP_NETWORK_POLICY')
or (query_text ilike '% set network_policy%' or
query_text ilike '% unset network_policy%')
and query_type != 'SELECT' and query_type != 'UNKNOWN'
order by end_time desc;

OR logic is doing full table scan. I think you want:

select user_name || ' made the following Network Policy change on ' || end_time || ' [' || query_text || ']' as Events
from SNOWFLAKE.ACCOUNT_USAGE.query_history where execution_status = 'SUCCESS'
and
(
query_type in ('CREATE_NETWORK_POLICY', 'ALTER_NETWORK_POLICY', 'DROP_NETWORK_POLICY')
or
(query_text ilike '% set network_policy%' or query_text ilike '% unset network_policy%')
)
and query_type != 'SELECT' and query_type != 'UNKNOWN'
order by end_time desc;

[sfc-gh-vtimofeenko]

Thank you very much for the report. I'll double check that the output of the updated query matches the current version and if so -- will update the code.

[sfc-gh-vtimofeenko]

Looks like pushing this predicate from the top actually makes the scan of QUERY_HISTORY worse. On a couple of my test accounts moving the OR inside the first AND predicate slows the query down by a factor of 2 -- probably because the first term:

and query_type in ('CREATE_NETWORK_POLICY', 'ALTER_NETWORK_POLICY', 'DROP_NETWORK_POLICY')
or (query_text ilike '% set network_policy%' or
query_text ilike '% unset network_policy%')

allows not evaluating the or term at all.

Can you share some performance timings (maybe with query IDs in slack) from your account in case my test results are specific to my QUERY_HISTORY?