EXPLAIN expression
The EXPLAIN expression displays the plan for the SELECT query that will be executed if this query is run. The plan is a tree of statements that are executed in a specific order to produce the result of the query.
When EXPLAIN is run, the SELECT query is not actually executed, so the plan displays only the expected result sizes for each statement, calculated by heuristics.
Examples
-
A simple
SELECTquery:EXPLAIN SELECT 1explain "Local plan: ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ _col1 │ │ │ │ ~1 row │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ DUMMY_SCAN │ └───────────────────────────┘ Execution settings: session_settings = {'threads': 16, 'memory_limit': '32GB', 'http_timeout': 30, 'http_retries': 20, 'http_retry_wait_ms': 500, 'pin_threads': 'off'} file_cache_mode = FileCacheMode.AUTO cache_mode_threshold = 0.6 file_row_group_size = 1000000 row_group_column_gap = 1048576 use_file_cache = True use_duck_cache = True local_db_cache_bytes = 107374182400 reshuffle_bin_rows = 300000000 " -
A
SELECTquery against a single table with grouping, filtering and sorting:EXPLAIN SELECT NameOfStation, COUNT(*) AS exit_count FROM data_mos_ru.metro_exits GROUP BY NameOfStation HAVING COUNT(*) > 10 ORDER BY exit_count DESC;explain "Local plan: ┌───────────────────────────┐ │ ORDER_BY │ │ ──────────────────── │ │ count(1) DESC │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ (count(1) > 10) │ │ │ │ ~146 rows │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ HASH_GROUP_BY │ │ ──────────────────── │ │ Groups: #0 │ │ │ │ Aggregates: │ │ count_star() │ │ │ │ ~731 rows │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ PROJECTION │ │ ──────────────────── │ │ NameOfStation │ │ │ │ ~1,157 rows │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ ICEBERG_SCAN │ │ ──────────────────── │ │ Function: │ │ ICEBERG_SCAN │ │ │ │ Projections: │ │ NameOfStation │ │ │ │ ~1,157 rows │ └───────────────────────────┘ Scan data_mos_ru.metro_exits: columns = {'NameOfStation'} filter = (True) skip_cache = False Execution settings: session_settings = {'threads': 16, 'memory_limit': '32GB', 'http_timeout': 30, 'http_retries': 20, 'http_retry_wait_ms': 500, 'pin_threads': 'off'} file_cache_mode = FileCacheMode.AUTO cache_mode_threshold = 0.6 file_row_group_size = 1000000 row_group_column_gap = 1048576 use_file_cache = True use_duck_cache = True local_db_cache_bytes = 107374182400 reshuffle_bin_rows = 300000000 " -
A
SELECTquery with joining two tables, grouping, filtering and sorting:EXPLAIN SELECT mpt.NameOfStation, SUM(mpt.IncomingPassengers) AS IncomingPassengers, SUM(mpt.OutgoingPassengers) AS OutgoingPassengers FROM data_mos_ru.metro_passenger_traffic mpt JOIN ( SELECT NameOfStation FROM data_mos_ru.metro_exits GROUP BY NameOfStation HAVING COUNT(*) > 10 ) me ON mpt.NameOfStation = me.NameOfStation WHERE mpt.Year = '2024' GROUP BY mpt.NameOfStation ORDER BY IncomingPassengers DESC;explain "Local plan: ┌───────────────────────────┐ │ ORDER_BY │ │ ──────────────────── │ │ sum(mpt.IncomingPassengers│ │ ) DESC │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ HASH_GROUP_BY │ │ ──────────────────── │ │ Groups: #0 │ │ │ │ Aggregates: │ │ sum(#1) │ │ sum(#2) │ │ │ │ ~137 rows │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ PROJECTION │ │ ──────────────────── │ │ NameOfStation │ │ IncomingPassengers │ │ OutgoingPassengers │ │ │ │ ~139 rows │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ HASH_JOIN │ │ ──────────────────── │ │ Join Type: INNER │ │ │ │ Conditions: ├──────────────┐ │ NameOfStation = │ │ │ NameOfStation │ │ │ │ │ │ ~139 rows │ │ └─────────────┬─────────────┘ │ ┌─────────────┴─────────────┐┌─────────────┴─────────────┐ │ ICEBERG_SCAN ││ PROJECTION │ │ ──────────────────── ││ ──────────────────── │ │ Function: ││ #0 │ │ ICEBERG_SCAN ││ │ │ ││ │ │ Projections: ││ │ │ NameOfStation ││ │ │ IncomingPassengers ││ │ │ OutgoingPassengers ││ │ │ ││ │ │ Filters: Year='2024' ││ │ │ ││ │ │ ~1,106 rows ││ ~146 rows │ └───────────────────────────┘└─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ (count(1) > 10) │ │ │ │ ~146 rows │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ HASH_GROUP_BY │ │ ──────────────────── │ │ Groups: #0 │ │ │ │ Aggregates: │ │ count_star() │ │ │ │ ~731 rows │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ PROJECTION │ │ ──────────────────── │ │ NameOfStation │ │ │ │ ~1,157 rows │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ ICEBERG_SCAN │ │ ──────────────────── │ │ Function: │ │ ICEBERG_SCAN │ │ │ │ Projections: │ │ NameOfStation │ │ │ │ ~1,157 rows │ └───────────────────────────┘ Scan data_mos_ru.metro_exits: columns = {'NameOfStation'} filter = (True) skip_cache = False Scan data_mos_ru.metro_passenger_traffic: columns = {'OutgoingPassengers', 'IncomingPassengers', 'Year', 'NameOfStation'} filter = (""Year"" = '2024') skip_cache = False Execution settings: session_settings = {'threads': 16, 'memory_limit': '32GB', 'http_timeout': 30, 'http_retries': 20, 'http_retry_wait_ms': 500, 'pin_threads': 'off'} file_cache_mode = FileCacheMode.AUTO cache_mode_threshold = 0.6 file_row_group_size = 1000000 row_group_column_gap = 1048576 use_file_cache = True use_duck_cache = True local_db_cache_bytes = 107374182400 reshuffle_bin_rows = 300000000 "