CODEWITHSUNDEEP
snowflake-schemasnowflake-cloud-data-platform
pcsutar
pcsutar

Reputation: 1821

Some operations like TableScan, InnerJoin, JoinFilter are not present in the output of the Snowflake EXPLAIN query

I am facing one issue with Snowflake EXPLAIN query. As mentioned in snowflake documentation I have executed below queries in snowflake instance but its giving different output.

Create the tables:

create table z1 (id integer);
create table z2 (id integer);
create table z3 (id integer);

Generate the EXPLAIN plan in tabular format for the query:

explain using tabular select z1.id, z2.id 
    from z1, z2
    where z2.id = z1.id;

Expected output:

+------+----+--------+-------------+------------------------------+-------+--------------------------+-----------------+--------------------+---------------+
| step | id | parent | operation   | objects                      | alias | expressions              | partitionsTotal | partitionsAssigned | bytesAssigned |
|------+----+--------+-------------+------------------------------+-------+--------------------------+-----------------+--------------------+---------------|
| NULL | NULL |   NULL | GlobalStats | NULL                         | NULL  | NULL                     |               2 |                  2 |          1024 |
|    1 |  0 |   NULL | Result      | NULL                         | NULL  | Z1.ID, Z2.ID             |            NULL |               NULL |          NULL |
|    1 |  1 |      0 | InnerJoin   | NULL                         | NULL  | joinKey: (Z2.ID = Z1.ID) |            NULL |               NULL |          NULL |
|    1 |  2 |      1 | TableScan   | TESTDB.TEMPORARY_DOC_TEST.Z2 | NULL  | ID                       |               1 |                  1 |           512 |
|    1 |  3 |      1 | JoinFilter  | NULL                         | NULL  | joinKey: (Z2.ID = Z1.ID) |            NULL |               NULL |          NULL |
|    1 |  4 |      3 | TableScan   | TESTDB.TEMPORARY_DOC_TEST.Z1 | NULL  | ID                       |               1 |                  1 |           512 |
+------+----+--------+-------------+------------------------------+-------+--------------------------+-----------------+--------------------+---------------+

Actual output:

+-----+----+-------+-----------+--------+------+------------+----------------+-------------------+--------------+
|step |id  |parent |operation  |objects |alias |expressions |partitionsTotal |partitionsAssigned |bytesAssigned |
+-----+----+-------+-----------+--------+------+------------+----------------+-------------------+--------------+
|NULL |NULL|NULL   |GlobalStats|NULL    |NULL  |NULL        |0               |0                  |0             |
|1    |0   |NULL   |Result     |NULL    |NULL  |Z1.ID, Z2.ID|NULL            |NULL               |NULL          |
|1    |1   |0      |Generator  |NULL    |NULL  |0           |NULL            |NULL               |NULL          |
+-----+--- +-------+-----------+--------+------+------------+----------------+-------------------+--------------+

In actual output I am not able to see results for operations like TableScan, JoinFilter, InnerJoin etc. I am not sure, if I have to do any settings in our snowflake instance?

We are planning to use the output of EXPLAIN to extract table names from SELECT query.

Upvotes: 1

Views: 220

Answers (1)

Gokhan Atil
Gokhan Atil

Reputation: 10039

Because all tables are empty, Snowflake SQL Optimizer sees that the tables will return 0 rows; instead of reading from these tables, it uses a generator as the data source. It may skip any unnecessary operations such as joins... It may happen if there are only a few rows in the table.

For example, this one will show both z1 and z2:

create or replace table z1 (id integer);
create or replace table z2 (id integer);

insert into z1 values (1 ),(2);
insert into z2 values (1 );

explain using tabular select z1.id, z2.id 
    from z1, z2
    where z2.id = z1.id;

Let's include z3 in the query:

create or replace table z1 (id integer);
create or replace table z2 (id integer);
create or replace table z3 (id integer);

insert into z1 values (1 ),(2);
insert into z2 values (1 );
insert into z3 values (1000 );

explain using tabular select z1.id, z2.id 
    from z1, z2
    where z2.id = z1.id
    and z1.id < (select z3.id from z3);

As you can see, z3 is not in the explain plan!

Let's add one more row to z3 and re-run the explain plan:

insert into z3 values (1);

explain using tabular select z1.id, z2.id 
    from z1, z2
    where z2.id = z1.id
    and z1.id < (select z3.id from z3);

Now you should see it there.

So extracting table names from EXPLAIN is not a good idea. Check this view:

https://docs.snowflake.com/en/sql-reference/account-usage/access_history.html

Upvotes: 1

Related Questions