page84
66
Bloom Filter
Snowflake implements Bloom filters for probabilistic (not deterministic) testing of
whether an element exists within a set of data and may return one of two outcomes.
•
The element is possibly in the set of data and may contain false-
positives.
•
The element is definitely not in the set of data and enables pruning,
leading to improved query performance.
A full explanation of Bloom filters is beyond the scope of this book; you can find
more information at
https://en.wikipedia.org/wiki/Bloom_filter
.
Explain Plan
Before you learn about “bad” query profiles, you will investigate tooling provided
to return—but not invoke—the execution plan for the current statement. To do this,
Snowflake provides the
EXPLAIN
keyword, which may be prefixed to any
SELECT
statement, and by so doing, the query is only evaluated. With the query plan available,
you can examine the quality of the execution plan.
It is important to note
EXPLAIN
is a metadata operation and therefore does not
require a warehouse. However, like all other metadata operations, cloud service credits
are consumed.
You will use
EXPLAIN
as you progress through the remainder of this chapter to
investigate and identify both poorly constructed and badly executing queries.
To illustrate how
EXPLAIN
works, let’s reuse the earlier known-good example query
referencing
v_supplier_part
. In this example, you will request the
TABULAR
output but
might instead prefer JSON output.
EXPLAIN USING TABULAR
SELECT part_name,
available_quantity AS avail_qty,
unit_price,
supplier_account_balance AS acct_bal,
supplier_name
ChapTer 3
The Query profiler