Getting a query plan

• Example of query modification

Get a query plan:

ydb table query explain \
  -q "SELECT season_id, episode_id, title
  FROM episodes
  WHERE series_id = 1
  AND season_id > 1
  ORDER BY season_id, episode_id
  LIMIT 3"

The main section of the query plan, tables, contains information about querying tables. Reads are described in the reads section and writes in the writes section. The key characteristic of any table query is its type.

Types of reads:

• FullScan: Full table scan. All entries on all shards are read.
• Scan: A read of a certain range of entries.
• Lookup: A read by key or key prefix.
• MultiLookup: Multiple reads by key or key prefix. Supported, for example, in JOINs.

Types of writes:

• Upsert: Add a single entry.
• MultiUpsert: Add multiple entries.
• Erase: A single delete by key.
• MultiErase: Multiple deletes.

Let’s take the query plan from the example above.
The lookup_by parameter shows what columns (key or key prefix) reads are made by.
The scan_by parameter shows what columns a read of all entries in a certain range of values is made by.
The columns parameter lists the columns whose values will be read from the table.

Example of query modification

Adjust the query so that you get only the first seasons of all the series:

ydb table query explain \
  -q "SELECT sa.title AS season_title, sr.title AS series_title, sr.series_id, sa.season_id 
  FROM seasons AS sa 
  INNER JOIN series AS sr ON sa.series_id = sr.series_id 
  WHERE sa.season_id = 1"

This query plan implies that a FullScan is made for the seasons table and multiple reads are made for the series table (the MultiLookup type) by the key series_id (lookup_by). The MultiLookup read type and the lookup_by section indicate that the series table is subject to multiple reads by the series_id key.