search

Snowflake-Labs / Sentry

Apache License 2.0
11 stars 8 forks source link

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

Open sfc-gh-jpfeiffer opened 5 months ago

sfc-gh-jpfeiffer commented 5 months ago

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 commented 5 months ago

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 commented 3 months ago

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?