optimize_skip_unused_shard.sql

select version();
-- ┌─version()───┐
-- │ 20.3.15.133 │
-- └─────────────┘

create table ranges_data engine=Memory() as select number key, number value from numbers(100);
create dictionary default.ranges_dict (key UInt64, value UInt64 default 1) primary key key source(clickhouse(host '127.0.0.1' port 9000 table 'ranges_data' db 'default' user 'default' password '')) lifetime(0) layout(cache(size_in_cells 100));
create table dist as system.one engine=Distributed('test_cluster_two_shards', system, one, dictGetUInt64('default.ranges_dict', 'value', toUInt64(dummy)));

SELECT *
FROM dist
WHERE dummy IN tuple(2)
SETTINGS optimize_skip_unused_shards = 1;

-- [fa9796262ce6] 2020.10.16 12:41:50.196921 [ 71 ] {c5df12c8-b9b7-4074-8f3b-424d0bbeeef6} <Debug> executeQuery: (from [::1]:49928) SELECT * FROM dist WHERE dummy IN tuple(2) SETTINGS optimize_skip_unused_shards = 1
-- ↗ Progress: 0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.) [fa9796262ce6] 2020.10.16 12:41:50.197432 [ 71 ] {c5df12c8-b9b7-4074-8f3b-424d0bbeeef6} <Trace> AccessRightsContext (default): Access granted: SELECT(dummy) ON default.dist
-- [fa9796262ce6] 2020.10.16 12:41:50.197800 [ 71 ] {c5df12c8-b9b7-4074-8f3b-424d0bbeeef6} <Trace> AccessRightsContext (default): Access granted: SELECT(dummy) ON default.dist
-- [fa9796262ce6] 2020.10.16 12:41:50.197922 [ 71 ] {c5df12c8-b9b7-4074-8f3b-424d0bbeeef6} <Debug> StorageDistributed: Reading from default.dist: Skipping irrelevant shards - the query will be sent to the following shards of the cluster (shard numbers):  [1]
-- [fa9796262ce6] 2020.10.16 12:41:50.198313 [ 71 ] {c5df12c8-b9b7-4074-8f3b-424d0bbeeef6} <Trace> AccessRightsContext (default): Access granted: SELECT(dummy) ON system.one
-- [fa9796262ce6] 2020.10.16 12:41:50.198379 [ 71 ] {c5df12c8-b9b7-4074-8f3b-424d0bbeeef6} <Trace> InterpreterSelectQuery: FetchColumns -> WithMergeableState
-- [fa9796262ce6] 2020.10.16 12:41:50.198486 [ 71 ] {c5df12c8-b9b7-4074-8f3b-424d0bbeeef6} <Trace> InterpreterSelectQuery: WithMergeableState -> Complete
-- [fa9796262ce6] 2020.10.16 12:41:50.198919 [ 71 ] {c5df12c8-b9b7-4074-8f3b-424d0bbeeef6} <Information> executeQuery: Read 1 rows, 1.00 B in 0.002 sec., 525 rows/sec., 525.69 B/sec.
-- [fa9796262ce6] 2020.10.16 12:41:50.199008 [ 71 ] {c5df12c8-b9b7-4074-8f3b-424d0bbeeef6} <Debug> MemoryTracker: Peak memory usage (for query): 0.00 B.
-- Ok.