EXPLAIN

This page documents the preview version (v2.23). Preview includes features under active development and is for development and testing only. For production, use the stable version (v2024.1). To learn more, see Versioning.

Synopsis

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

Syntax

Diagram

EXPLAINselectupdateinsertdelete

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

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.

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.

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.

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.

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.

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.

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')

Note

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.

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')

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')

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

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

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')

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

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')

See also