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.
lookup_by parameter shows what columns (key or key prefix) reads are made by.
scan_by parameter shows what columns a read of all entries in a certain range of values is made by.
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