Getting a query plan
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.