Snowflake SQL Tutorial 152 - EXPLAIN Doesn't Run It, And I Counted The Rows To Prove It

Snowflake SQL · Episode 152

In this post we are going to cover EXPLAIN in Snowflake.

EXPLAIN shows you what Snowflake would do with a statement, without doing it. You write the word in front of your statement and you get back the plan instead of the result.

That second half, the not doing it, is the part worth proving rather than asserting. So we are going to prove it on a table we can count, and count it after every step.

We will start with the smallest plan there is and build it up step by step. Every query below was run on a real Snowflake account, and every result and every error you see is what it gave back.

Episode 152 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page
  1. Let's create some sample data
  2. Let's start with the smallest EXPLAIN
  3. Now let's add a filter and an ORDER BY
  4. Now let's group it
  5. Now a join, which is where plans get interesting
  6. What does a CTE look like in a plan?
  7. A window function, and then a strange number
  8. Now the other two formats
  9. Now let's prove it really does not run the statement
  10. And the same thing for a CREATE TABLE AS SELECT
  11. So if it does not run anything, does it check anything?
  12. And here is where the compiling stops
  13. Now let's read a plan back as a table
  14. Now partition pruning, and a mistake of mine first
  15. So let's build a table that is in more than one partition
  16. Now one partition out of twelve
  17. And now two predicates that prune nothing
  18. One last thing: one column against four
  19. And let's leave the schema the way we found it

Let's create some sample data

First let's see where the session is pointing.

Query
SELECT CURRENT_DATABASE()  AS THE_DATABASE,
       CURRENT_SCHEMA()    AS THE_SCHEMA,
       CURRENT_VERSION()   AS THE_VERSION;
Result1 row · 3 columns
THE_DATABASETHE_SCHEMATHE_VERSION
DEMO_DBPUBLIC10.35.101

Now a small sales table. Small on purpose, so the plans stay readable.

Sample data
CREATE OR REPLACE TABLE SALES_T152 (
    SALE_ID   INT,
    REGION_ID INT,
    AMOUNT    NUMBER(10,2)
);
Result1 row · 1 column
status
Table SALES_T152 successfully created.
Sample data
INSERT INTO SALES_T152 (SALE_ID, REGION_ID, AMOUNT)
VALUES (1, 10, 100.00), (2, 20, 250.50), (3, 10,  75.25),
       (4, 30, 310.00), (5, 20,  45.00), (6, 10, 190.75),
       (7, 30,  60.00), (8, 20, 125.00);
Result1 row · 1 column
number of rows inserted
8
Query
SELECT * FROM SALES_T152 ORDER BY SALE_ID;
Result8 rows · 3 columns
SALE_IDREGION_IDAMOUNT
110100.00
220250.50
31075.25
430310.00
52045.00
610190.75
73060.00
820125.00

And a second table to join to.

Sample data
CREATE OR REPLACE TABLE REGIONS_T152 (
    REGION_ID   INT,
    REGION_NAME VARCHAR(20)
);
Result1 row · 1 column
status
Table REGIONS_T152 successfully created.
Sample data
INSERT INTO REGIONS_T152 (REGION_ID, REGION_NAME)
VALUES (10, 'north'), (20, 'south'), (30, 'east');
Result1 row · 1 column
number of rows inserted
3
Query
SELECT * FROM REGIONS_T152 ORDER BY REGION_ID;
Result3 rows · 2 columns
REGION_IDREGION_NAME
10north
20south
30east

Eight sales and three regions. That is the whole dataset for the first half of this post.

Let's start with the smallest EXPLAIN

Take a SELECT * and put the word EXPLAIN in front of it.

Query
EXPLAIN SELECT * FROM SALES_T152;
Result3 rows · 10 columns · scroll →
stepidparentOperatorsoperationobjectsaliasexpressionspartitionsTotalpartitionsAssignedbytesAssigned
NULLNULLNULLGlobalStatsNULLNULLNULL111024
10NULLResultNULLNULLSALES_T152.SALE_ID, SALES_T152.REGION_ID, SALES_T152.AMOUNTNULLNULLNULL
11[0]TableScanDEMO_DB.PUBLIC.SALES_T152NULLSALE_ID, REGION_ID, AMOUNT111024

Three rows. And the first thing to say is that you read a plan from the bottom up.

The TableScan at the bottom happens first. The Result at the top is what comes out.

And GlobalStats is not a step at all. It is a summary row, which is why its step and id are empty while every real operator has a number.

There is a spelling you will see written down more often, EXPLAIN USING TABULAR. Let's check what it changes.

Query
EXPLAIN USING TABULAR SELECT * FROM SALES_T152;
Result3 rows · 10 columns · scroll →
stepidparentOperatorsoperationobjectsaliasexpressionspartitionsTotalpartitionsAssignedbytesAssigned
NULLNULLNULLGlobalStatsNULLNULLNULL111024
10NULLResultNULLNULLSALES_T152.SALE_ID, SALES_T152.REGION_ID, SALES_T152.AMOUNTNULLNULLNULL
11[0]TableScanDEMO_DB.PUBLIC.SALES_T152NULLSALE_ID, REGION_ID, AMOUNT111024

Nothing. The same grid, value for value. So TABULAR is already the default and the keyword adds nothing but typing.

Now let's add a filter and an ORDER BY

Let's watch the plan grow.

Query
EXPLAIN SELECT SALE_ID, AMOUNT
  FROM SALES_T152
 WHERE AMOUNT > 100
 ORDER BY AMOUNT DESC;
Result5 rows · 10 columns · scroll →
stepidparentOperatorsoperationobjectsaliasexpressionspartitionsTotalpartitionsAssignedbytesAssigned
NULLNULLNULLGlobalStatsNULLNULLNULL111024
10NULLResultNULLNULLSALES_T152.SALE_ID, SALES_T152.AMOUNTNULLNULLNULL
11[0]SortNULLNULLSALES_T152.AMOUNT DESC NULLS FIRSTNULLNULLNULL
12[1]FilterNULLNULLSALES_T152.AMOUNT > 100NULLNULLNULL
13[2]TableScanDEMO_DB.PUBLIC.SALES_T152NULLSALE_ID, AMOUNT111024

Four operators now, and reading up from the bottom: scan the table, filter it, sort it, hand back the result.

Look at what the Sort step says it sorts by. We wrote ORDER BY AMOUNT DESC. The plan says DESC NULLS FIRST. The nulls part is not something we typed. Snowflake filled it in, and now we can see it.

And here is the same query run for real, so the result is on the page next to its plan.

Query
SELECT SALE_ID, AMOUNT
  FROM SALES_T152
 WHERE AMOUNT > 100
 ORDER BY AMOUNT DESC;
Result4 rows · 2 columns
SALE_IDAMOUNT
4310.00
2250.50
6190.75
8125.00

Now let's group it

A GROUP BY adds an Aggregate step, and that step spells out its own working.

Query
EXPLAIN SELECT REGION_ID, COUNT(*) AS HOW_MANY, SUM(AMOUNT) AS TOTAL_AMOUNT
  FROM SALES_T152
 GROUP BY REGION_ID
 ORDER BY REGION_ID;
Result5 rows · 10 columns · scroll →
stepidparentOperatorsoperationobjectsaliasexpressionspartitionsTotalpartitionsAssignedbytesAssigned
NULLNULLNULLGlobalStatsNULLNULLNULL111024
10NULLResultNULLNULLSALES_T152.REGION_ID, COUNT(*), SUM(SALES_T152.AMOUNT)NULLNULLNULL
11[0]SortNULLNULLSALES_T152.REGION_ID ASC NULLS LASTNULLNULLNULL
12[1]AggregateNULLNULLaggExprs: [COUNT(*), SUM(SALES_T152.AMOUNT)], groupKeys: [SALES_T152.REGION_ID]NULLNULLNULL
13[2]TableScanDEMO_DB.PUBLIC.SALES_T152NULLREGION_ID, AMOUNT111024

Read the Aggregate row's expression. It lists both aggregates and the group key, with the table name in front of each column. The plan is quoting our own query back to us in its own words.

Query
SELECT REGION_ID, COUNT(*) AS HOW_MANY, SUM(AMOUNT) AS TOTAL_AMOUNT
  FROM SALES_T152
 GROUP BY REGION_ID
 ORDER BY REGION_ID;
Result3 rows · 3 columns
REGION_IDHOW_MANYTOTAL_AMOUNT
103366.00
203420.50
302370.00

Now a join, which is where plans get interesting

Query
EXPLAIN SELECT S.SALE_ID, R.REGION_NAME, S.AMOUNT
  FROM SALES_T152 S
  JOIN REGIONS_T152 R ON R.REGION_ID = S.REGION_ID
 ORDER BY S.SALE_ID;
Result7 rows · 10 columns · scroll →
stepidparentOperatorsoperationobjectsaliasexpressionspartitionsTotalpartitionsAssignedbytesAssigned
NULLNULLNULLGlobalStatsNULLNULLNULL222048
10NULLResultNULLNULLS.SALE_ID, R.REGION_NAME, S.AMOUNTNULLNULLNULL
11[0]SortNULLNULLS.SALE_ID ASC NULLS LASTNULLNULLNULL
12[1]InnerJoinNULLNULLjoinKey: (R.REGION_ID = S.REGION_ID)NULLNULLNULL
13[2]TableScanDEMO_DB.PUBLIC.REGIONS_T152RREGION_ID, REGION_NAME111024
14[2]JoinFilterNULLNULLjoinKey: (R.REGION_ID = S.REGION_ID)NULLNULLNULL
15[4]TableScanDEMO_DB.PUBLIC.SALES_T152SSALE_ID, REGION_ID, AMOUNT111024

An InnerJoin step, with the join key written out. Two TableScan rows, one per table, each naming the table it reads and the alias we gave it.

And a step we never asked for: JoinFilter. Snowflake put that in by itself.

Now look at GlobalStats. Its partitionsTotal is two, and each of the two scans below it says one. Hold on to that, because we are about to see the same number somewhere stranger.

Query
SELECT S.SALE_ID, R.REGION_NAME, S.AMOUNT
  FROM SALES_T152 S
  JOIN REGIONS_T152 R ON R.REGION_ID = S.REGION_ID
 ORDER BY S.SALE_ID;
Result8 rows · 3 columns
SALE_IDREGION_NAMEAMOUNT
1north100.00
2south250.50
3north75.25
4east310.00
5south45.00
6north190.75
7east60.00
8south125.00

What does a CTE look like in a plan?

Let's write a WITH clause in front of a GROUP BY and look for it in the plan.

Query
EXPLAIN WITH BIG_ONES AS (
    SELECT SALE_ID, REGION_ID, AMOUNT FROM SALES_T152 WHERE AMOUNT > 100
)
SELECT REGION_ID, COUNT(*) AS HOW_MANY FROM BIG_ONES GROUP BY REGION_ID;
Result5 rows · 10 columns · scroll →
stepidparentOperatorsoperationobjectsaliasexpressionspartitionsTotalpartitionsAssignedbytesAssigned
NULLNULLNULLGlobalStatsNULLNULLNULL111024
10NULLResultNULLNULLSALES_T152.REGION_ID, COUNT(*)NULLNULLNULL
11[0]AggregateNULLNULLaggExprs: [COUNT(*)], groupKeys: [SALES_T152.REGION_ID]NULLNULLNULL
12[1]FilterNULLNULLSALES_T152.AMOUNT > 100NULLNULLNULL
13[2]TableScanDEMO_DB.PUBLIC.SALES_T152NULLREGION_ID, AMOUNT111024

This is worth stopping on. There is no CTE step in that plan at all. No operator named after our BIG_ONES, and nothing holding its rows.

The operators are the ones the inner query needed: scan, filter, aggregate, result. The CTE was folded straight in, and the plan is how we know.

So a CTE is a way of writing a query. It is not a thing the engine makes.

A window function, and then a strange number

A window function does get its own step, and the whole OVER clause goes inside it.

Query
EXPLAIN SELECT SALE_ID, AMOUNT,
       SUM(AMOUNT) OVER (PARTITION BY REGION_ID ORDER BY SALE_ID) AS RUNNING_TOTAL
  FROM SALES_T152;
Result4 rows · 10 columns · scroll →
stepidparentOperatorsoperationobjectsaliasexpressionspartitionsTotalpartitionsAssignedbytesAssigned
NULLNULLNULLGlobalStatsNULLNULLNULL111024
10NULLResultNULLNULLSALES_T152.SALE_ID, SALES_T152.AMOUNT, SUM(SALES_T152.AMOUNT) OVER (PARTITION BY SALES_T152.REGION_ID ORDER BY SALES_T152.SALE_ID ASC NULLS LAST)NULLNULLNULL
11[0]WindowFunctionNULLNULLSUM(SALES_T152.AMOUNT) OVER (PARTITION BY SALES_T152.REGION_ID ORDER BY SALES_T152.SALE_ID ASC NULLS LAST)NULLNULLNULL
12[1]TableScanDEMO_DB.PUBLIC.SALES_T152NULLSALE_ID, REGION_ID, AMOUNT111024

Now a UNION ALL, and read GlobalStats carefully.

Query
EXPLAIN SELECT SALE_ID FROM SALES_T152 WHERE AMOUNT > 200
UNION ALL
SELECT SALE_ID FROM SALES_T152 WHERE AMOUNT < 70;
Result7 rows · 10 columns · scroll →
stepidparentOperatorsoperationobjectsaliasexpressionspartitionsTotalpartitionsAssignedbytesAssigned
NULLNULLNULLGlobalStatsNULLNULLNULL222048
10NULLResultNULLNULLUNION_ALL(SALES_T152.SALE_ID, SALES_T152.SALE_ID)NULLNULLNULL
11[0]UnionAllNULLNULLNULLNULLNULLNULL
12[1]FilterNULLNULLSALES_T152.AMOUNT > 200NULLNULLNULL
13[2]TableScanDEMO_DB.PUBLIC.SALES_T152NULLSALE_ID, AMOUNT111024
14[1]FilterNULLNULLSALES_T152.AMOUNT < 70NULLNULLNULL
15[4]TableScanDEMO_DB.PUBLIC.SALES_T152NULLSALE_ID, AMOUNT111024

Two branches under one UnionAll step, and both of them are reading the same table. And partitionsTotal says two.

The number people misread

partitionsTotal is counted per scan and added up. It is not how many partitions a table is in. A union of one table against itself reports two, and the two-table join above reported two as well. If you read that column as a table fact you will be wrong about it.

Now the other two formats

There are three formats and we have been using one. Here is TEXT, on the smallest query there is.

Query
EXPLAIN USING TEXT SELECT 1 AS ONE;
Result1 row · 1 column
content
GlobalStats: partitionsTotal=0 partitionsAssigned=0 bytesAssigned=0 Operations: 1:0 ->Result 1 1:1 ->Generator 1

One column, called content, with the whole plan indented inside it. Let's do it on a real query.

Query
EXPLAIN USING TEXT SELECT REGION_ID, COUNT(*) AS HOW_MANY
  FROM SALES_T152 GROUP BY REGION_ID;
Result1 row · 1 column
content
GlobalStats: partitionsTotal=1 partitionsAssigned=1 bytesAssigned=1024 Operations: 1:0 ->Result SALES_T152.REGION_ID, COUNT(*) 1:1 ->Aggregate aggExprs: [COUNT(*)], groupKeys: [SALES_T152.REGION_ID] 1:2 ->TableScan DEMO_DB.PUBLIC.SALES_T152 REGION_ID {partitionsTotal=1, partitionsAssigned=1, bytesAssigned=1024}

GlobalStats first, then the operators, each on its own line with its step and id in front. And the partition figures are in braces on the scan line, which is where they belong.

That is the form to use when you want to paste a plan into a message to somebody.

Now JSON.

Query
EXPLAIN USING JSON SELECT 1 AS ONE;
Result1 row · 1 column
content
{"GlobalStats":{"partitionsTotal":0,"partitionsAssigned":0,"bytesAssigned":0},"Operations":[[{"id":0,"operation":"Result","expressions":["1"]},{"id":1,"operation":"Generator","expressions":["1"],"parentOperators":[0]}]]}

One column again, and it is a real JSON object, so we can pull it apart. There is a function that gives us the same thing without the EXPLAIN keyword, so let's ask it for the top-level keys.

Query
SELECT OBJECT_KEYS(PARSE_JSON(
           SYSTEM$EXPLAIN_PLAN_JSON('SELECT * FROM SALES_T152'))) AS TOP_LEVEL_KEYS;
Result1 row · 1 column
TOP_LEVEL_KEYS
[ "GlobalStats", "Operations" ]

Two keys, and that is the whole structure. Let's look inside one of them.

Query
SELECT PARSE_JSON(
           SYSTEM$EXPLAIN_PLAN_JSON('SELECT * FROM SALES_T152')):GlobalStats
       AS GLOBAL_STATS;
Result1 row · 1 column
GLOBAL_STATS
{ "bytesAssigned": 1024, "partitionsAssigned": 1, "partitionsTotal": 1 }

Exactly the three numbers we have been reading off the summary row of every grid so far.

And there is a function that turns the JSON back into the text form.

Query
SELECT SYSTEM$EXPLAIN_JSON_TO_TEXT(
           SYSTEM$EXPLAIN_PLAN_JSON('SELECT * FROM SALES_T152')) AS THE_PLAN_AS_TEXT;
Result1 row · 1 column
THE_PLAN_AS_TEXT
GlobalStats: bytesAssigned=1024 partitionsAssigned=1 partitionsTotal=1 Operations: 1:0 ->Result SALES_T152.SALE_ID, SALES_T152.REGION_ID, SALES_T152.AMOUNT 1:1 ->TableScan DEMO_DB.PUBLIC.SALES_T152 SALE_ID, REGION_ID, AMOUNT {partitionsTotal=1, partitionsAssigned=1, bytesAssigned=1024}

So: TABULAR to read, TEXT to paste, JSON to parse.

Now let's ask for a fourth one that does not exist.

Query
EXPLAIN USING SPREADSHEET SELECT 1 AS ONE;
Query failed — Snowflake returned an error
001119 (42601): SQL compilation error: error line 1 at position 4
 Invalid explain plan format 'SPREADSHEET'

001119, and it names the format we made up. Clear enough.

Now let's prove it really does not run the statement

This is the reason the post exists. Let's make an empty table with one column and count it after every single step.

Sample data
CREATE OR REPLACE TABLE PROBE_T152 (N INT);
Result1 row · 1 column
status
Table PROBE_T152 successfully created.
Query
SELECT COUNT(*) AS ROWS_IN_PROBE FROM PROBE_T152;
Result1 row · 1 column
ROWS_IN_PROBE
0

Empty. Now let's EXPLAIN an insert of five rows into it.

Query
EXPLAIN INSERT INTO PROBE_T152 (N)
SELECT SEQ4() FROM TABLE(GENERATOR(ROWCOUNT => 5));
Result4 rows · 10 columns · scroll →
stepidparentOperatorsoperationobjectsaliasexpressionspartitionsTotalpartitionsAssignedbytesAssigned
NULLNULLNULLGlobalStatsNULLNULLNULL000
10NULLResultNULLNULLnumber of rows insertedNULLNULLNULL
11[0]InsertDEMO_DB.PUBLIC.PROBE_T152NULLNULLNULLNULLNULL
12[1]GeneratorNULLNULL5NULLNULLNULL

There is the plan, with an Insert step naming our table. And have a look at the Result row's expression. It says number of rows inserted, which is the heading the real statement would have given back.

Now let's count the table again. Nothing in between. Just the count.

Query
SELECT COUNT(*) AS ROWS_IN_PROBE FROM PROBE_T152;
Result1 row · 1 column
ROWS_IN_PROBE
0

Still empty. The plan was made. The insert was not.

Now let's run the same insert for real, so the contrast comes from one session and not from my word for it.

Query
INSERT INTO PROBE_T152 (N)
SELECT SEQ4() FROM TABLE(GENERATOR(ROWCOUNT => 5));
Result1 row · 1 column
number of rows inserted
5
Query
SELECT COUNT(*) AS ROWS_IN_PROBE FROM PROBE_T152;
Result1 row · 1 column
ROWS_IN_PROBE
5

Five. Now let's EXPLAIN a delete of everything in it.

Query
EXPLAIN DELETE FROM PROBE_T152 WHERE N >= 0;
Result4 rows · 10 columns · scroll →
stepidparentOperatorsoperationobjectsaliasexpressionspartitionsTotalpartitionsAssignedbytesAssigned
NULLNULLNULLGlobalStatsNULLNULLNULL100
10NULLResultNULLNULLnumber of rows deletedNULLNULLNULL
11[0]DeleteDEMO_DB.PUBLIC.PROBE_T152NULLNULLNULLNULLNULL
12[1]TableScanDEMO_DB.PUBLIC.PROBE_T152NULLNULL100
Query
SELECT COUNT(*) AS ROWS_IN_PROBE FROM PROBE_T152;
Result1 row · 1 column
ROWS_IN_PROBE
5

Still five. It planned the delete and left the rows alone.

Let's do two more, an update and a merge.

Query
EXPLAIN UPDATE PROBE_T152 SET N = N + 1000 WHERE N = 0;
Result5 rows · 10 columns · scroll →
stepidparentOperatorsoperationobjectsaliasexpressionspartitionsTotalpartitionsAssignedbytesAssigned
NULLNULLNULLGlobalStatsNULLNULLNULL111024
10NULLResultNULLNULLnumber of rows updated, number of multi-joined rows updatedNULLNULLNULL
11[0]UpdateDEMO_DB.PUBLIC.PROBE_T152NULLNULLNULLNULLNULL
12[1]FilterNULLNULLPROBE_T152.N = 0NULLNULLNULL
13[2]TableScanDEMO_DB.PUBLIC.PROBE_T152NULLN111024
Query
EXPLAIN MERGE INTO PROBE_T152 T
  USING (SELECT 0 AS N) S ON T.N = S.N
   WHEN MATCHED THEN UPDATE SET T.N = 999
   WHEN NOT MATCHED THEN INSERT (N) VALUES (S.N);
Result8 rows · 10 columns · scroll →
stepidparentOperatorsoperationobjectsaliasexpressionspartitionsTotalpartitionsAssignedbytesAssigned
NULLNULLNULLGlobalStatsNULLNULLNULL111024
10NULLResultNULLNULLnumber of rows inserted, number of rows updatedNULLNULLNULL
11[0]MergeDEMO_DB.PUBLIC.PROBE_T152NULLNULLNULLNULLNULL
12[1]LeftOuterJoinNULLNULLjoinKey: (0 = T.N)NULLNULLNULL
13[2]GeneratorNULLNULL1NULLNULLNULL
14[2]FilterNULLNULLT.N IS NOT NULLNULLNULLNULL
15[4]JoinFilterNULLNULLjoinKey: (0 = T.N)NULLNULLNULL
16[5]TableScanDEMO_DB.PUBLIC.PROBE_T152TN111024

And now the rows, one last time.

Query
SELECT N FROM PROBE_T152 ORDER BY N;
Result5 rows · 1 column
N
0
1
2
3
4

All five of them, after three more explains. That is the claim proved by counting rather than asserted.

And the same thing for a CREATE TABLE AS SELECT

Let's explain a create into a table name that has never existed.

Query
EXPLAIN CREATE OR REPLACE TABLE NEVER_CREATED_T152 AS SELECT * FROM SALES_T152;
Result4 rows · 10 columns · scroll →
stepidparentOperatorsoperationobjectsaliasexpressionspartitionsTotalpartitionsAssignedbytesAssigned
NULLNULLNULLGlobalStatsNULLNULLNULL111024
10NULLResultNULLNULLNULLNULLNULLNULL
11[0]CreateTableAsSelectNULLNULLDEMO_DB.PUBLIC.NEVER_CREATED_T152NULLNULLNULL
12[1]TableScanDEMO_DB.PUBLIC.SALES_T152NULLSALE_ID, REGION_ID, AMOUNT111024

The plan names it. A CreateTableAsSelect step, with the table it would create in the expressions column.

Now let's ask the catalog whether that table is there.

Query
SELECT COUNT(*) AS DID_IT_GET_CREATED
  FROM DEMO_DB.INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'NEVER_CREATED_T152';
Result1 row · 1 column
DID_IT_GET_CREATED
0

It never was.

So if it does not run anything, does it check anything?

Yes. It compiles. Let's explain a query against a table that does not exist.

Query
EXPLAIN SELECT * FROM NO_SUCH_TABLE_T152;
Query failed — Snowflake returned an error
002003 (42S02): SQL compilation error:
Object 'NO_SUCH_TABLE_T152' does not exist or not authorized. Your primary role ACCOUNTADMIN or one of your secondary roles must have at least one privilege granted on TABLE NO_SUCH_TABLE_T152.

002003. So the plan never got made at all. EXPLAIN refused at compile time, exactly as the real query would have.

A column that does not exist.

Query
EXPLAIN SELECT NO_SUCH_COLUMN FROM SALES_T152;
Query failed — Snowflake returned an error
000904 (42000): SQL compilation error: error line 1 at position 15
invalid identifier 'NO_SUCH_COLUMN'

And a column that exists, but in the other table.

Query
EXPLAIN SELECT SALE_ID + REGION_NAME FROM SALES_T152;
Query failed — Snowflake returned an error
000904 (42000): SQL compilation error: error line 1 at position 25
invalid identifier 'REGION_NAME'

The same code, naming the column it could not resolve from the table we gave it.

Something that is not even syntax.

Query
EXPLAIN SELECT FROM WHERE SALES_T152;
Query failed — Snowflake returned an error
001003 (42000): SQL compilation error:
syntax error line 1 at position 15 unexpected 'FROM'.
syntax error line 1 at position 20 unexpected 'WHERE'.

001003, and it found two problems in one line rather than stopping at the first.

And an aggregate with no GROUP BY.

Query
EXPLAIN SELECT REGION_ID, COUNT(*) FROM SALES_T152;
Query failed — Snowflake returned an error
000979 (42601): SQL compilation error:
[SALES_T152.REGION_ID] is not a valid group by expression
Worth having

So EXPLAIN is a free compile check on a statement you are not ready to run. Point it at the query you just wrote, and a missing table, a misspelled column or an ungrouped aggregate comes back straight away, without a warehouse touching a row.

And here is where the compiling stops

Let's explain a drop of a table that does not exist, which ought to be the same kind of refusal as the first one.

Query
EXPLAIN DROP TABLE IF EXISTS DEMO_DB.PUBLIC.NO_SUCH_TABLE_T152;
Result2 rows · 10 columns · scroll →
stepidparentOperatorsoperationobjectsaliasexpressionspartitionsTotalpartitionsAssignedbytesAssigned
NULLNULLNULLGlobalStatsNULLNULLNULL000
10NULLResultNULLNULLNULLNULLNULLNULL

No error at all. A GlobalStats row of zeroes and one bare Result row with nothing in it.

A SHOW command gives the same empty shape.

Query
EXPLAIN SHOW TABLES LIKE '%\\_T152';
Result2 rows · 10 columns · scroll →
stepidparentOperatorsoperationobjectsaliasexpressionspartitionsTotalpartitionsAssignedbytesAssigned
NULLNULLNULLGlobalStatsNULLNULLNULL000
10NULLResultNULLNULLNULLNULLNULLNULL

Now a CALL. There is a procedure here for it to point at.

Create the procedure
CREATE OR REPLACE PROCEDURE COUNTER_T152()
RETURNS NUMBER
LANGUAGE SQL
AS
$$
BEGIN
    LET HOW_MANY NUMBER;
    SELECT COUNT(*) INTO :HOW_MANY FROM SALES_T152;
    RETURN :HOW_MANY;
END;
$$;
Result1 row · 1 column
status
Function COUNTER_T152 successfully created.
Query
EXPLAIN CALL COUNTER_T152();
Query failed — Snowflake returned an error
001003 (42000): SQL compilation error:
syntax error line 1 at position 8 unexpected 'CALL'.

A syntax error. And explaining an EXPLAIN is the same.

Query
EXPLAIN EXPLAIN SELECT 1 AS ONE;
Query failed — Snowflake returned an error
001003 (42000): SQL compilation error:
syntax error line 1 at position 8 unexpected 'EXPLAIN'.

So EXPLAIN takes queries and DML. It does not take procedural statements, and for some DDL it gives you a plan with nothing in it rather than an opinion.

One more shape worth seeing: a query with no table in it at all.

Query
EXPLAIN SELECT 1 AS ONE, CURRENT_DATE() AS TODAYS_DATE;
Result3 rows · 10 columns · scroll →
stepidparentOperatorsoperationobjectsaliasexpressionspartitionsTotalpartitionsAssignedbytesAssigned
NULLNULLNULLGlobalStatsNULLNULLNULL000
10NULLResultNULLNULL1, '2026-10-06'NULLNULLNULL
11[0]GeneratorNULLNULL1NULLNULLNULL

A Generator instead of a scan, zeroes in the summary row, and the Result expression already holds the date, worked out at compile time.

Now let's read a plan back as a table

A plan is a result, and a result has an id, so a plan can be read as rows. Let's explain something first.

Query
EXPLAIN SELECT SALE_ID, AMOUNT FROM SALES_T152 WHERE AMOUNT > 100;
Result4 rows · 10 columns · scroll →
stepidparentOperatorsoperationobjectsaliasexpressionspartitionsTotalpartitionsAssignedbytesAssigned
NULLNULLNULLGlobalStatsNULLNULLNULL111024
10NULLResultNULLNULLSALES_T152.SALE_ID, SALES_T152.AMOUNTNULLNULLNULL
11[0]FilterNULLNULLSALES_T152.AMOUNT > 100NULLNULLNULL
12[1]TableScanDEMO_DB.PUBLIC.SALES_T152NULLSALE_ID, AMOUNT111024

And now read it back.

Query
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
Result4 rows · 10 columns · scroll →
stepidparentOperatorsoperationobjectsaliasexpressionspartitionsTotalpartitionsAssignedbytesAssigned
NULLNULLNULLGlobalStatsNULLNULLNULL111024
10NULLResultNULLNULLSALES_T152.SALE_ID, SALES_T152.AMOUNTNULLNULLNULL
11[0]FilterNULLNULLSALES_T152.AMOUNT > 100NULLNULLNULL
12[1]TableScanDEMO_DB.PUBLIC.SALES_T152NULLSALE_ID, AMOUNT111024

The same plan, as ordinary rows. Which means it filters and sorts and projects like anything else. Let's explain the query again and then take two columns out of the plan.

Query
EXPLAIN SELECT SALE_ID, AMOUNT FROM SALES_T152 WHERE AMOUNT > 100;
Result4 rows · 10 columns · scroll →
stepidparentOperatorsoperationobjectsaliasexpressionspartitionsTotalpartitionsAssignedbytesAssigned
NULLNULLNULLGlobalStatsNULLNULLNULL111024
10NULLResultNULLNULLSALES_T152.SALE_ID, SALES_T152.AMOUNTNULLNULLNULL
11[0]FilterNULLNULLSALES_T152.AMOUNT > 100NULLNULLNULL
12[1]TableScanDEMO_DB.PUBLIC.SALES_T152NULLSALE_ID, AMOUNT111024
Query
SELECT "operation", "objects"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "id";
Result4 rows · 2 columns
operationobjects
ResultNULL
FilterNULL
TableScanDEMO_DB.PUBLIC.SALES_T152
GlobalStatsNULL

Much easier to read, and now the TableScan row names the table it read with nothing else in the way.

The pattern

Two things to notice in that query. The column names are in double quotes, because a plan's column names are lower case. And the ORDER BY "id" is what puts GlobalStats at the bottom, because its id is empty.
That is the pattern for the rest of this post: EXPLAIN, then RESULT_SCAN, then pick the columns you want. The full grid is ten columns wide and we only ever want four of them.

Now partition pruning, and a mistake of mine first

Partition pruning is what EXPLAIN is actually for most of the time. Snowflake stores a table in micro-partitions, and if your WHERE clause lets it skip some of them, the plan says so.

So I built a bigger table for this, with four columns and a padding string, so the partition numbers would mean something.

Sample data
CREATE OR REPLACE TABLE BIG_T152 AS
SELECT SEQ4()                                        AS ID,
       UNIFORM(1, 100, RANDOM(42))                   AS BUCKET,
       DATEADD('day', MOD(SEQ4(), 1000), DATE '2020-01-01') AS THE_DAY,
       RANDSTR(60, RANDOM(7))                        AS PADDING
  FROM TABLE(GENERATOR(ROWCOUNT => 400000));
Result1 row · 1 column
status
Table BIG_T152 successfully created.
Query
SELECT COUNT(*) AS HOW_MANY_ROWS, MIN(ID) AS LOWEST_ID, MAX(ID) AS HIGHEST_ID
  FROM BIG_T152;
Result1 row · 3 columns
HOW_MANY_ROWSLOWEST_IDHIGHEST_ID
4000000399999
Query
SELECT ID, BUCKET, THE_DAY, LEFT(PADDING, 12) AS PADDING_STARTS_WITH
  FROM BIG_T152 ORDER BY ID LIMIT 3;
Result3 rows · 4 columns
IDBUCKETTHE_DAYPADDING_STARTS_WITH
0962020-01-01CNviDwBa5Nd0
172020-01-02B0eR3HeCN5Tr
282020-01-03YgJEs1Kcqf5F

Now, before looking at a single plan, let's ask how many micro-partitions that table is in.

Query
SELECT PARSE_JSON(SYSTEM$CLUSTERING_INFORMATION('BIG_T152', '(ID)')
       ):total_partition_count AS TOTAL_PARTITION_COUNT;
Result1 row · 1 column
TOTAL_PARTITION_COUNT
1

One. The whole thing landed in a single micro-partition, so there is nothing to prune and nothing to show.

Here is what that does to a plan with a narrow range on it.

Query
EXPLAIN SELECT COUNT(*) AS HOW_MANY FROM BIG_T152 WHERE ID < 2000;
Result5 rows · 10 columns · scroll →
stepidparentOperatorsoperationobjectsaliasexpressionspartitionsTotalpartitionsAssignedbytesAssigned
NULLNULLNULLGlobalStatsNULLNULLNULL1125846272
10NULLResultNULLNULLCOUNT(*)NULLNULLNULL
11[0]AggregateNULLNULLaggExprs: [COUNT(*)]NULLNULLNULL
12[1]FilterNULLNULLBIG_T152.ID < 2000NULLNULLNULL
13[2]TableScanDEMO_DB.PUBLIC.BIG_T152NULLID1125846272

One of one. Every plan in that section that had a predicate on it said one of one, which taught nobody anything.

That is my arithmetic and not a Snowflake limit, and the fix is a bigger table with a wider column. So let's do it again.

So let's build a table that is in more than one partition

This part was captured in a second session, later the same day, by which time the account was on a newer Snowflake version.

Query
SELECT CURRENT_DATABASE()  AS THE_DATABASE,
       CURRENT_SCHEMA()    AS THE_SCHEMA,
       CURRENT_VERSION()   AS THE_VERSION;
Result1 row · 3 columns
THE_DATABASETHE_SCHEMATHE_VERSION
DEMO_DBPUBLIC10.36.101

Ten times the rows, and twice the padding.

Sample data
CREATE OR REPLACE TABLE BIG_T152B AS
SELECT SEQ4()                                             AS ID,
       UNIFORM(1, 100, RANDOM(42))                        AS BUCKET,
       DATEADD('day', MOD(SEQ4(), 2000), DATE '2019-01-01') AS THE_DAY,
       RANDSTR(120, RANDOM(7))                            AS PADDING
  FROM TABLE(GENERATOR(ROWCOUNT => 4000000));
Result1 row · 1 column
status
Table BIG_T152B successfully created.
Query
SELECT COUNT(*) AS HOW_MANY_ROWS, MIN(ID) AS LOWEST_ID, MAX(ID) AS HIGHEST_ID
  FROM BIG_T152B;
Result1 row · 3 columns
HOW_MANY_ROWSLOWEST_IDHIGHEST_ID
400000003999999
Query
SELECT ID, BUCKET, THE_DAY, LEFT(PADDING, 14) AS PADDING_STARTS_WITH
  FROM BIG_T152B ORDER BY ID LIMIT 3;
Result3 rows · 4 columns
IDBUCKETTHE_DAYPADDING_STARTS_WITH
0962019-01-01CNviDwBa5Nd093
172019-01-02B0eR3HeCN5Trd2
282019-01-03YgJEs1Kcqf5FUp

And how big that is on disk, which we are going to want later.

Query
SELECT TABLE_NAME, ROW_COUNT, BYTES
  FROM DEMO_DB.INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'BIG_T152B';
Result1 row · 3 columns
TABLE_NAMEROW_COUNTBYTES
BIG_T152B4000000375130624

Now the partition count, before any plan.

Query
SELECT PARSE_JSON(SYSTEM$CLUSTERING_INFORMATION('BIG_T152B', '(ID)')
       ):total_partition_count AS TOTAL_PARTITION_COUNT;
Result1 row · 1 column
TOTAL_PARTITION_COUNT
12

Twelve. And here is the whole object that number came out of, which is worth seeing once.

Query
SELECT SYSTEM$CLUSTERING_INFORMATION('BIG_T152B', '(ID)') AS CLUSTERING_INFORMATION;
Result1 row · 1 column
CLUSTERING_INFORMATION
{ "cluster_by_keys" : "LINEAR(ID)", "notes" : "Clustering key columns contain high cardinality key ID which might result in expensive re-clustering. Consider reducing the cardinality of clustering keys. Please refer to https://docs.snowflake.net/manuals/user-guide/tables-clustering-keys.html for more information.", "total_partition_count" : 12, "total_constant_partition_count" : 0, "average_overlaps" : 0.0, "average_depth" : 1.0, "version" : "OPTIMA", "clustering_errors" : [ ] }

And let's ask a plan the same question

The obvious way to ask a plan how big the table is would be to explain a COUNT(*) over the lot. Let's try that and read it carefully.

Query
EXPLAIN SELECT COUNT(*) AS HOW_MANY FROM BIG_T152B;
Result3 rows · 10 columns · scroll →
stepidparentOperatorsoperationobjectsaliasexpressionspartitionsTotalpartitionsAssignedbytesAssigned
NULLNULLNULLGlobalStatsNULLNULLNULL000
10NULLResultNULLNULL4000000NULLNULLNULL
11[0]GeneratorNULLNULL1NULLNULLNULL
Query
SELECT "operation", "objects", "partitionsTotal", "partitionsAssigned",
       "bytesAssigned"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "id";
Result3 rows · 5 columns
operationobjectspartitionsTotalpartitionsAssignedbytesAssigned
ResultNULLNULLNULLNULL
GeneratorNULLNULLNULLNULL
GlobalStatsNULL000

Zeroes, and there is no TableScan in that plan at all. A Generator instead, and the Result expression is the row count itself.

So Snowflake answered that COUNT(*) out of what it already knows about the table, and never planned to read a partition. Which means this plan cannot tell us the partition count, and we have to put a predicate on it before the number turns up.

Now one partition out of twelve

Let's ask for a narrow range on ID, which is the column the rows were written in order of.

Query
EXPLAIN SELECT COUNT(*) AS HOW_MANY FROM BIG_T152B WHERE ID < 2000;
Result5 rows · 10 columns · scroll →
stepidparentOperatorsoperationobjectsaliasexpressionspartitionsTotalpartitionsAssignedbytesAssigned
NULLNULLNULLGlobalStatsNULLNULLNULL12132267264
10NULLResultNULLNULLCOUNT(*)NULLNULLNULL
11[0]AggregateNULLNULLaggExprs: [COUNT(*)]NULLNULLNULL
12[1]FilterNULLNULLBIG_T152B.ID < 2000NULLNULLNULL
13[2]TableScanDEMO_DB.PUBLIC.BIG_T152BNULLID12132267264
Query
SELECT "operation", "objects", "partitionsTotal", "partitionsAssigned",
       "bytesAssigned"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "id";
Result5 rows · 5 columns
operationobjectspartitionsTotalpartitionsAssignedbytesAssigned
ResultNULLNULLNULLNULL
AggregateNULLNULLNULLNULL
FilterNULLNULLNULLNULL
TableScanDEMO_DB.PUBLIC.BIG_T152B12132267264
GlobalStatsNULL12132267264

There it is. The scan says it would read one partition out of twelve, and the bytes it would read are a small fraction of the bytes the table takes up on disk, which we counted two sections ago.

And here is the query run for real, so the answer is on the page too.

Query
SELECT COUNT(*) AS HOW_MANY FROM BIG_T152B WHERE ID < 2000;
Result1 row · 1 column
HOW_MANY
2000

Now let's take a narrow range from the middle of the table instead of the start.

Query
EXPLAIN SELECT COUNT(*) AS HOW_MANY
  FROM BIG_T152B WHERE ID BETWEEN 2000000 AND 2001000;
Result5 rows · 10 columns · scroll →
stepidparentOperatorsoperationobjectsaliasexpressionspartitionsTotalpartitionsAssignedbytesAssigned
NULLNULLNULLGlobalStatsNULLNULLNULL12132267776
10NULLResultNULLNULLCOUNT(*)NULLNULLNULL
11[0]AggregateNULLNULLaggExprs: [COUNT(*)]NULLNULLNULL
12[1]FilterNULLNULL(BIG_T152B.ID >= 2000000) AND (BIG_T152B.ID <= 2001000)NULLNULLNULL
13[2]TableScanDEMO_DB.PUBLIC.BIG_T152BNULLID12132267776
Query
SELECT "operation", "objects", "partitionsTotal", "partitionsAssigned",
       "bytesAssigned"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "id";
Result5 rows · 5 columns
operationobjectspartitionsTotalpartitionsAssignedbytesAssigned
ResultNULLNULLNULLNULL
AggregateNULLNULLNULLNULL
FilterNULLNULLNULLNULL
TableScanDEMO_DB.PUBLIC.BIG_T152B12132267776
GlobalStatsNULL12132267776

One of twelve again. So pruning is not a trick that only works at the beginning of a table. That is the thing to take away.

Query
SELECT COUNT(*) AS HOW_MANY
  FROM BIG_T152B WHERE ID BETWEEN 2000000 AND 2001000;
Result1 row · 1 column
HOW_MANY
1001

And now two predicates that prune nothing

First the random padding string, which cannot possibly prune, because the values are random and every partition holds some of every shape.

Query
EXPLAIN SELECT COUNT(*) AS HOW_MANY
  FROM BIG_T152B WHERE PADDING = 'no such string';
Result5 rows · 10 columns · scroll →
stepidparentOperatorsoperationobjectsaliasexpressionspartitionsTotalpartitionsAssignedbytesAssigned
NULLNULLNULLGlobalStatsNULLNULLNULL1212375130624
10NULLResultNULLNULLCOUNT(*)NULLNULLNULL
11[0]AggregateNULLNULLaggExprs: [COUNT(*)]NULLNULLNULL
12[1]FilterNULLNULLBIG_T152B.PADDING = 'no such string'NULLNULLNULL
13[2]TableScanDEMO_DB.PUBLIC.BIG_T152BNULLPADDING1212375130624
Query
SELECT "operation", "objects", "partitionsTotal", "partitionsAssigned",
       "bytesAssigned"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "id";
Result5 rows · 5 columns
operationobjectspartitionsTotalpartitionsAssignedbytesAssigned
ResultNULLNULLNULLNULL
AggregateNULLNULLNULLNULL
FilterNULLNULLNULLNULL
TableScanDEMO_DB.PUBLIC.BIG_T152B1212375130624
GlobalStatsNULL1212375130624

Twelve of twelve. The whole table, and the bytes figure is now the whole table's bytes.

Query
SELECT COUNT(*) AS HOW_MANY FROM BIG_T152B WHERE PADDING = 'no such string';
Result1 row · 1 column
HOW_MANY
0

No rows. A full scan to find nothing, which is the worst shape a query can have.

And now the date column

Query
EXPLAIN SELECT COUNT(*) AS HOW_MANY FROM BIG_T152B WHERE THE_DAY = DATE '2019-01-05';
Result5 rows · 10 columns · scroll →
stepidparentOperatorsoperationobjectsaliasexpressionspartitionsTotalpartitionsAssignedbytesAssigned
NULLNULLNULLGlobalStatsNULLNULLNULL1212375130624
10NULLResultNULLNULLCOUNT(*)NULLNULLNULL
11[0]AggregateNULLNULLaggExprs: [COUNT(*)]NULLNULLNULL
12[1]FilterNULLNULLBIG_T152B.THE_DAY = '2019-01-05'NULLNULLNULL
13[2]TableScanDEMO_DB.PUBLIC.BIG_T152BNULLTHE_DAY1212375130624
Query
SELECT "operation", "objects", "partitionsTotal", "partitionsAssigned",
       "bytesAssigned"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "id";
Result5 rows · 5 columns
operationobjectspartitionsTotalpartitionsAssignedbytesAssigned
ResultNULLNULLNULLNULL
AggregateNULLNULLNULLNULL
FilterNULLNULLNULLNULL
TableScanDEMO_DB.PUBLIC.BIG_T152B1212375130624
GlobalStatsNULL1212375130624

Twelve of twelve as well. And this one needs explaining rather than reporting, because it looks like dates not pruning and that is not what happened.

Look back at how that column was made. The day is the start date plus MOD of the row number. MOD cycles, so the dates run through their whole range again and again all the way down the table.

Which means every partition's lowest and highest date cover the day we asked for, and not one of them can be skipped.

The thing people get wrong about this

Pruning is about how the values are physically arranged in the table, not about the type of the column. The same date predicate on a table written in date order would prune. This one cannot, and the reason is in my CREATE statement rather than in Snowflake.

One last thing: one column against four

Same predicate, and the narrow range that prunes to one partition. First asking for one column.

Query
EXPLAIN SELECT ID FROM BIG_T152B WHERE ID < 2000;
Result4 rows · 10 columns · scroll →
stepidparentOperatorsoperationobjectsaliasexpressionspartitionsTotalpartitionsAssignedbytesAssigned
NULLNULLNULLGlobalStatsNULLNULLNULL12132267264
10NULLResultNULLNULLBIG_T152B.IDNULLNULLNULL
11[0]FilterNULLNULLBIG_T152B.ID < 2000NULLNULLNULL
12[1]TableScanDEMO_DB.PUBLIC.BIG_T152BNULLID12132267264
Query
SELECT "operation", "expressions", "partitionsAssigned", "bytesAssigned"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "id";
Result4 rows · 4 columns
operationexpressionspartitionsAssignedbytesAssigned
ResultBIG_T152B.IDNULLNULL
FilterBIG_T152B.ID < 2000NULLNULL
TableScanID132267264
GlobalStatsNULL132267264

Now asking for all four.

Query
EXPLAIN SELECT * FROM BIG_T152B WHERE ID < 2000;
Result4 rows · 10 columns · scroll →
stepidparentOperatorsoperationobjectsaliasexpressionspartitionsTotalpartitionsAssignedbytesAssigned
NULLNULLNULLGlobalStatsNULLNULLNULL12132267264
10NULLResultNULLNULLBIG_T152B.ID, BIG_T152B.BUCKET, BIG_T152B.THE_DAY, BIG_T152B.PADDINGNULLNULLNULL
11[0]FilterNULLNULLBIG_T152B.ID < 2000NULLNULLNULL
12[1]TableScanDEMO_DB.PUBLIC.BIG_T152BNULLID, BUCKET, THE_DAY, PADDING12132267264
Query
SELECT "operation", "expressions", "partitionsAssigned", "bytesAssigned"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "id";
Result4 rows · 4 columns
operationexpressionspartitionsAssignedbytesAssigned
ResultBIG_T152B.ID, BIG_T152B.BUCKET, BIG_T152B.THE_DAY, BIG_T152B.PADDINGNULLNULL
FilterBIG_T152B.ID < 2000NULLNULL
TableScanID, BUCKET, THE_DAY, PADDING132267264
GlobalStatsNULL132267264

The expressions column changed, so the plan knows perfectly well that one query wants one column and the other wants four. And bytesAssigned is identical, to the byte.

I am going to say exactly what that is and no more. The plan does not report a projection saving. That is all this grid tells us. It is not a claim about what the engine does with your columns when the query actually runs.

Which is a good habit to finish on. A plan is an estimate written down before anything happened. Read what it says, and do not read what it does not.

And let's leave the schema the way we found it

Query
SELECT CURRENT_DATABASE()    AS THE_DATABASE,
       CURRENT_SCHEMA()      AS THE_SCHEMA,
       CURRENT_TRANSACTION() AS ANY_OPEN_TRANSACTION;
Result1 row · 3 columns
THE_DATABASETHE_SCHEMAANY_OPEN_TRANSACTION
DEMO_DBPUBLICNULL
Query
SELECT COUNT(*) AS PROBE_TABLES_LEFT
  FROM DEMO_DB.INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'PUBLIC' AND ENDSWITH(TABLE_NAME, '_T152');
Result1 row · 1 column
PROBE_TABLES_LEFT
0
Query
SELECT COUNT(*) AS PROBE_TABLES_LEFT
  FROM DEMO_DB.INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'PUBLIC' AND ENDSWITH(TABLE_NAME, '_T152B');
Result1 row · 1 column
PROBE_TABLES_LEFT
0

Same database and schema, nothing open, and neither session's tables are left behind.

Key points

  • EXPLAIN gives you the plan instead of the result, and you read a plan from the bottom up.
  • GlobalStats is a summary row, not a step. Its step and id are empty.
  • USING TABULAR is the default and changes nothing.
  • The Sort step shows you the nulls ordering Snowflake filled in for you, and the Aggregate step quotes your aggregates and group keys back.
  • A join adds a JoinFilter step nobody asked for.
  • A CTE does not appear in the plan. It gets folded in, and the plan is how you know.
  • partitionsTotal is counted per scan and added up. A UNION ALL over one table reports two.
  • Three formats: TABULAR to read, TEXT to paste, JSON to parse. A fourth is 001119.
  • EXPLAIN really does not run the statement. The probe table was empty before explaining an insert and empty after, and still held every row after explaining a delete, an update and a merge.
  • EXPLAIN on a CREATE TABLE AS SELECT names the table it would make and does not make it.
  • It still compiles, which makes it a free check: a missing table is 002003, a missing column 000904, an ungrouped aggregate 000979, nonsense 001003.
  • But not for everything. A DROP of a missing table and a SHOW both give an empty plan and no error, and CALL and EXPLAIN are syntax errors.
  • A plan can be read back as rows with RESULT_SCAN, and the column names need double quotes.
  • A COUNT(*) with no predicate is answered without a TableScan, so its plan reports no partitions at all.
  • A narrow range on the column the rows were written in order of pruned to one partition out of twelve, from the start of the table and from the middle of it.
  • A random string predicate scanned all twelve and found nothing, and so did the date predicate, because MOD put the whole date range in every partition.
  • The one-column and four-column plans report the same bytesAssigned. The plan does not report a projection saving, and that is all that says.
Next in this series

In the next post we look properly at the thing we used to read every plan on this page: RESULT_SCAN. Reading a query's result back by its id, reading SHOW output as rows, and the reason the same query ran twice can give you two different answers.

Every query, every result grid and every error pane on this page was captured from a live Snowflake account running the demo script for this episode. The first session, which is everything up to and including the four-hundred-thousand-row table, ran on version 10.35.101. The pruning section was captured again later the same day on a newer version, which the grid at the top of that section shows, and those captures are the ones whose table name ends in B. Every count in the text is read off one of the grids on this page. No account identifier, user name or query id appears anywhere on this page. Nothing is timed, and no plan on this page is compared to another one for speed. Behaviour follows the Snowflake EXPLAIN documentation.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Subscribe to: Post Comments (Atom)