EXPLAIN statement [YCQL]

Synopsis

Use the EXPLAIN statement to show the execution plan for a statement.

Syntax

Diagram

Grammar

explain ::= EXPLAIN { select | update | insert | delete }

Semantics

Where the target statement is one of the following: SELECT, UPDATE, INSERT, or DELETE.

Examples

Create the keyspace, tables and indexes.

Setup Table and indexes

CQL

cqlsh> CREATE KEYSPACE IF NOT EXISTS imdb;
cqlsh> CREATE TABLE IF NOT EXISTS imdb.movie_stats (
           movie_name text,
           movie_genre text,
           user_name text,
           user_rank int,
           last_watched timestamp,
           PRIMARY KEY (movie_genre, movie_name, user_name)
    ) WITH transactions = { 'enabled' : true };
cqlsh> CREATE INDEX IF NOT EXISTS most_watched_by_year
      ON imdb.movie_stats((movie_genre, last_watched), movie_name, user_name)
      INCLUDE(user_rank);
cqlsh> CREATE INDEX IF NOT EXISTS best_rated
      ON imdb.movie_stats((user_rank, movie_genre), movie_name, user_name)
      INCLUDE(last_watched);

Insert some rows.

CQL

cqlsh> USE imdb;
cqlsh:imdb> INSERT INTO movie_stats(movie_name, movie_genre, user_name, user_rank, last_watched)
            VALUES ('m1', 'g1', 'u1', 5, '2019-01-18');
cqlsh:imdb> INSERT INTO movie_stats(movie_name, movie_genre, user_name, user_rank, last_watched)
            VALUES ('m2', 'g2', 'u1', 4, '2019-01-17');
cqlsh:imdb> INSERT INTO movie_stats(movie_name, movie_genre, user_name, user_rank, last_watched)
            VALUES ('m3', 'g1', 'u2', 5, '2019-01-18');
cqlsh:imdb> INSERT INTO movie_stats(movie_name, movie_genre, user_name, user_rank, last_watched)
            VALUES ('m4', 'g1', 'u1', 2, '2019-02-27');

Explain query plans

If movie_genre, or movie_genre & movie_name, or movie_genre & movie_name & user_name are specified, the query should be served efficiently from the primary table.

CQL

cqlsh:imdb> EXPLAIN SELECT *
            FROM movie_stats
            WHERE movie_genre = 'g1';

QUERY PLAN
----------------------------------------
 Range Scan on imdb.movie_stats
   Key Conditions: (movie_genre = 'g1')

If movie_genre & last_watched are specified, then the query should be served efficiently from the most_watched_by_year index.

CQL

cqlsh:imdb> EXPLAIN SELECT *
            FROM movie_stats
            WHERE movie_genre = 'g1' and last_watched='2019-02-27';

QUERY PLAN
--------------------------------------------------------------------------
 Index Only Scan using imdb.most_watched_by_year on imdb.movie_stats
   Key Conditions: (movie_genre = 'g1') AND (last_watched = '2019-02-27')

If user_rank and movie_genre are specified then the query should be served efficiently from the best_rated index.

CQL

cqlsh:imdb> EXPLAIN SELECT *
            FROM movie_stats
            WHERE movie_genre = 'g2' and user_rank=5;

QUERY PLAN
--------------------------------------------------------------
 Index Only Scan using imdb.best_rated on imdb.movie_stats
   Key Conditions: (user_rank = '5') AND (movie_genre = 'g2')

Create non-covering index.

CQL

cqlsh:imdb> DROP INDEX best_rated;
cqlsh:imdb> CREATE INDEX IF NOT EXISTS best_rated
            ON imdb.movie_stats((user_rank, movie_genre), movie_name, user_name);

2-Step select. Using Index Scan as opposed to Index Only Scan.

CQL

cqlsh:imdb> EXPLAIN SELECT *
            FROM movie_stats
            WHERE movie_genre = 'g2' and user_rank=5;

 QUERY PLAN
--------------------------------------------------------------
 Index Scan using imdb.best_rated on imdb.movie_stats
   Key Conditions: (user_rank = '5') AND (movie_genre = 'g2')

INDEX SCAN: Filters rows using the index and then fetches the columns from the main table.

INDEX ONLY SCAN: Returns results by only consulting the index.

Other EXPLAIN SELECT types

QLName() for these expressions is not supported.

CQL

cqlsh:imdb> EXPLAIN SELECT * FROM movie_stats where movie_genre in ('g1', 'g2');

 QUERY PLAN
-------------------------------------------
 Range Scan on imdb.movie_stats
   Key Conditions: (movie_genre IN 'expr')

CQL

cqlsh:imdb> EXPLAIN SELECT COUNT(*) FROM movie_stats  WHERE movie_genre = 'g2' and user_rank=5;

 QUERY PLAN
--------------------------------------------------------------------
 Aggregate
   ->  Index Only Scan using imdb.best_rated on imdb.movie_stats
         Key Conditions: (user_rank = '5') AND (movie_genre = 'g2')

CQL

cqlsh:imdb> EXPLAIN SELECT * FROM movie_stats  WHERE movie_genre = 'g2' and user_rank = 5 LIMIT 5;

 QUERY PLAN
--------------------------------------------------------------------
 Limit
   ->  Index Only Scan using imdb.best_rated on imdb.movie_stats
         Key Conditions: (user_rank = '5') AND (movie_genre = 'g2')

INSERT example

CQL

cqlsh:imdb> EXPLAIN INSERT INTO movie_stats(movie_name, movie_genre, user_name, user_rank, last_watched)
            VALUES ('m4', 'g1', 'u1', 2, '2019-02-27');

 QUERY PLAN
----------------------------
 Insert on imdb.movie_stats

DELETE examples

CQL

cqlsh:imdb> explain delete from movie_stats  where movie_genre = 'g1' and movie_name = 'm1';

 QUERY PLAN
----------------------------------------------
 Delete on imdb.movie_stats
   ->  Range Scan on imdb.movie_stats
         Key Conditions: (movie_genre = 'g1')
         Filter: (movie_name = 'm1')

CQL

cqlsh:imdb> explain delete from movie_stats  where movie_genre = 'g1';

 QUERY PLAN
----------------------------------------------
 Delete on imdb.movie_stats
   ->  Range Scan on imdb.movie_stats
         Key Conditions: (movie_genre = 'g1')

UPDATE example

CQL

cqlsh:imdb> EXPLAIN UPDATE movie_stats SET user_rank = 1 WHERE movie_name = 'm1' and movie_genre = 'g1' and user_name = 'u1';

 QUERY PLAN
---------------------------------------------------------------------------------------------
 Update on imdb.movie_stats
   ->  Primary Key Lookup on imdb.movie_stats
         Key Conditions: (movie_genre = 'g1') AND (movie_name = 'm1') AND (user_name = 'u1')