Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Secondary index is slower than full scan #55653

Open
alexey-milovidov opened this issue Oct 16, 2023 · 10 comments
Open

Secondary index is slower than full scan #55653

alexey-milovidov opened this issue Oct 16, 2023 · 10 comments
Labels
performance

Comments

@alexey-milovidov
Copy link
Member 

CREATE TABLE index_test
(
    z UInt32,
    x UInt16 ALIAS mortonDecode(2, z).1,
    y UInt16 ALIAS mortonDecode(2, z).2,
    INDEX i_x (mortonDecode(2, z).1) TYPE minmax,
    INDEX i_y (mortonDecode(2, z).2) TYPE minmax
) ENGINE = MergeTree ORDER BY z;

INSERT INTO index_test SELECT number FROM numbers(0x100000000) WHERE rand() % 3 = 1;

-- primary key, 5 ms.
SELECT count() FROM index_test WHERE z >= 1000000000 AND z <= 1000100000;

-- secondary index, 1341 ms.
SELECT count() FROM index_test WHERE x >= 20000 AND x <= 20100 AND y >= 10000 AND y <= 10100;

-- full scan, 417 ms.
ALTER TABLE index_test DROP INDEX i_x, DROP INDEX i_y;
SELECT count() FROM index_test WHERE x >= 20000 AND x <= 20100 AND y >= 10000 AND y <= 10100;

Logs of the query with secondary index:

[milovidov-desktop] 2023.10.16 06:49:58.303367 [ 376016 ] {ca0840b5-ade6-4149-897b-67c8fd17b77c} <Debug> executeQuery: (from [::ffff:127.0.0.1]:59990) SELECT count() FROM index_test WHERE x >= 20000 AND x <= 20100 AND y >= 10000 AND y <= 10100 (stage: Complete)
[milovidov-desktop] 2023.10.16 06:49:58.304468 [ 376016 ] {ca0840b5-ade6-4149-897b-67c8fd17b77c} <Trace> InterpreterSelectQuery: The min valid primary key position for moving to the tail of PREWHERE is 0
[milovidov-desktop] 2023.10.16 06:49:58.304856 [ 376016 ] {ca0840b5-ade6-4149-897b-67c8fd17b77c} <Trace> ContextAccess (default): Access granted: SELECT(x, y) ON default.index_test
[milovidov-desktop] 2023.10.16 06:49:58.304928 [ 376016 ] {ca0840b5-ade6-4149-897b-67c8fd17b77c} <Trace> InterpreterSelectQuery: FetchColumns -> Complete
[milovidov-desktop] 2023.10.16 06:49:58.306240 [ 376016 ] {ca0840b5-ade6-4149-897b-67c8fd17b77c} <Debug> default.index_test (f8283f41-c765-4a2e-83fa-1688847d0c70) (SelectExecutor): Key condition: unknown, unknown, and, unknown, and, unknown, and
[milovidov-desktop] 2023.10.16 06:49:59.640236 [ 376016 ] {ca0840b5-ade6-4149-897b-67c8fd17b77c} <Debug> default.index_test (f8283f41-c765-4a2e-83fa-1688847d0c70) (SelectExecutor): Index `i_x` has dropped 173760/174761 granules.
[milovidov-desktop] 2023.10.16 06:49:59.640322 [ 376016 ] {ca0840b5-ade6-4149-897b-67c8fd17b77c} <Debug> default.index_test (f8283f41-c765-4a2e-83fa-1688847d0c70) (SelectExecutor): Index `i_y` has dropped 997/1001 granules.
[milovidov-desktop] 2023.10.16 06:49:59.640392 [ 376016 ] {ca0840b5-ade6-4149-897b-67c8fd17b77c} <Debug> default.index_test (f8283f41-c765-4a2e-83fa-1688847d0c70) (SelectExecutor): Selected 2/2 parts by partition key, 1 parts by primary key, 174761/174761 marks by primary key, 4 marks to read from 2 ranges
[milovidov-desktop] 2023.10.16 06:49:59.640431 [ 376016 ] {ca0840b5-ade6-4149-897b-67c8fd17b77c} <Trace> default.index_test (f8283f41-c765-4a2e-83fa-1688847d0c70) (SelectExecutor): Spreading mark ranges among streams (default reading)
[milovidov-desktop] 2023.10.16 06:49:59.640497 [ 376016 ] {ca0840b5-ade6-4149-897b-67c8fd17b77c} <Trace> default.index_test (f8283f41-c765-4a2e-83fa-1688847d0c70) (SelectExecutor): Reading 2 ranges in order from part all_1_1340_4_1343, approx. 32768 rows starting from 136921088
[milovidov-desktop] 2023.10.16 06:49:59.643213 [ 383074 ] {ca0840b5-ade6-4149-897b-67c8fd17b77c} <Trace> AggregatingTransform: Aggregating
[milovidov-desktop] 2023.10.16 06:49:59.643265 [ 383074 ] {ca0840b5-ade6-4149-897b-67c8fd17b77c} <Trace> Aggregator: An entry for key=4744199629802693815 found in cache: sum_of_sizes=1, median_size=1
[milovidov-desktop] 2023.10.16 06:49:59.643282 [ 383074 ] {ca0840b5-ade6-4149-897b-67c8fd17b77c} <Trace> Aggregator: Aggregation method: without_key
[milovidov-desktop] 2023.10.16 06:49:59.643337 [ 383074 ] {ca0840b5-ade6-4149-897b-67c8fd17b77c} <Trace> AggregatingTransform: Aggregated. 3359 to 1 rows (from 0.00 B) in 0.002588214 sec. (1297806.132 rows/sec., 0.00 B/sec.)
[milovidov-desktop] 2023.10.16 06:49:59.643358 [ 383074 ] {ca0840b5-ade6-4149-897b-67c8fd17b77c} <Trace> Aggregator: Merging aggregated data
┌─count()─┐
│    3359 │
└─────────┘
[milovidov-desktop] 2023.10.16 06:49:59.644367 [ 376016 ] {ca0840b5-ade6-4149-897b-67c8fd17b77c} <Debug> executeQuery: Read 32768 rows, 128.00 KiB in 1.341094 sec., 24433.783165087607 rows/sec., 95.44 KiB/sec.
[milovidov-desktop] 2023.10.16 06:49:59.644590 [ 376016 ] {ca0840b5-ade6-4149-897b-67c8fd17b77c} <Debug> MemoryTracker: Peak memory usage (for query): 4.48 MiB.
[milovidov-desktop] 2023.10.16 06:49:59.644607 [ 376016 ] {ca0840b5-ade6-4149-897b-67c8fd17b77c} <Debug> TCPHandler: Processed in 1.341629963 sec.

1 row in set. Elapsed: 1.341 sec. Processed 32.77 thousand rows, 131.07 KB (24.43 thousand rows/s., 97.72 KB/s.)
@alexey-milovidov
Copy link
Member Author 

clickhouse-benchmark --query_profiler_cpu_time_period_ns 1000000 <<< "SELECT count() FROM index_test WHERE x >= 20000 AND x <= 20100 AND y >= 10000 AND y <= 10100"

SELECT
    count(),
    arrayStringConcat(arrayMap(x -> concat(demangle(addressToSymbol(x)), '\n    ', addressToLine(x)), trace), '\n') AS sym
FROM system.trace_log
WHERE event_date = today() AND event_time >= now() - 300 AND query_id != '' AND trace_type = 'CPU'
GROUP BY trace
ORDER BY count() DESC
LIMIT 10
SETTINGS allow_introspection_functions = 1

Row 1:
──────
count(): 555
sym:     StackTrace::StackTrace(ucontext_t const&)
    ./build/./src/Common/StackTrace.cpp:286
DB::(anonymous namespace)::writeTraceInfo(DB::TraceType, int, siginfo_t*, void*)
    ./build/./src/Common/QueryProfiler.cpp:76

    

Row 2:
──────
count(): 28
sym:     CurrentMemoryTracker::allocNoThrow(long)
    ./build/./src/Common/CurrentMemoryTracker.cpp:66

Row 3:
──────
count(): 28
sym:     nallocx
    ./build/./contrib/jemalloc/src/jemalloc.c:4005

Row 4:
──────
count(): 26
sym:     operator new(unsigned long)
    ./build/./src/Common/new_delete.cpp:73

Row 5:
──────
count(): 26
sym:     operator new(unsigned long)
    ./build/./src/Common/new_delete.cpp:78

Row 6:
──────
count(): 25
sym:     DB::ColumnWithTypeAndName* std::__1::construct_at[abi:v15000]<DB::ColumnWithTypeAndName, DB::ColumnWithTypeAndName&, DB::ColumnWithTypeAndName*>(DB::ColumnWithTypeAndName*, DB::ColumnWithTypeAndName&)
    /home/milovidov/work/ClickHouse/build/programs/clickhouse
DB::IExecutableFunction::execute(std::__1::vector<DB::ColumnWithTypeAndName, std::__1::allocator<DB::ColumnWithTypeAndName>> const&, std::__1::shared_ptr<DB::IDataType const> const&, unsigned long, bool) const
    ./build/./contrib/llvm-project/libcxx/include/__memory/uninitialized_algorithms.h:539
DB::FunctionWithOptionalConstArg::execute(std::__1::vector<DB::ColumnWithTypeAndName, std::__1::allocator<DB::ColumnWithTypeAndName>> const&, std::__1::shared_ptr<DB::IDataType const> const&, unsigned long, bool) const
    ./build/./contrib/llvm-project/libcxx/include/__memory/shared_ptr.h:701
DB::applyFunction(std::__1::shared_ptr<DB::IFunctionBase const> const&, std::__1::shared_ptr<DB::IDataType const> const&, DB::FieldRef const&)
    ./build/./contrib/boost/boost/smart_ptr/intrusive_ptr.hpp:196
DB::KeyCondition::applyMonotonicFunctionsChainToRange(DB::Range, std::__1::vector<std::__1::shared_ptr<DB::IFunctionBase const>, std::__1::allocator<std::__1::shared_ptr<DB::IFunctionBase const>>> const&, std::__1::shared_ptr<DB::IDataType const>, bool)
    ./build/./src/Core/Range.h:21
DB::KeyCondition::checkInHyperrectangle(std::__1::vector<DB::Range, std::__1::allocator<DB::Range>> const&, std::__1::vector<std::__1::shared_ptr<DB::IDataType const>, std::__1::allocator<std::__1::shared_ptr<DB::IDataType const>>> const&) const
    ./build/./src/Core/Range.h:43
DB::MergeTreeIndexConditionMinMax::mayBeTrueOnGranule(std::__1::shared_ptr<DB::IMergeTreeIndexGranule>) const
    ./build/./src/Storages/MergeTree/MergeTreeIndexMinMax.cpp:0
DB::MergeTreeDataSelectExecutor::filterMarksUsingIndex(std::__1::shared_ptr<DB::IMergeTreeIndex const>, std::__1::shared_ptr<DB::IMergeTreeIndexCondition>, std::__1::shared_ptr<DB::IMergeTreeDataPart const>, DB::MarkRanges const&, DB::Settings const&, DB::MergeTreeReaderSettings const&, DB::MarkCache*, DB::UncompressedCache*, Poco::Logger*)
    ./build/./src/Storages/MergeTree/MergeTreeDataSelectExecutor.cpp:1704
DB::MergeTreeDataSelectExecutor::filterPartsByPrimaryKeyAndSkipIndexes(std::__1::vector<std::__1::shared_ptr<DB::IMergeTreeDataPart const>, std::__1::allocator<std::__1::shared_ptr<DB::IMergeTreeDataPart const>>>&&, std::__1::vector<std::__1::shared_ptr<DB::AlterConversions const>, std::__1::allocator<std::__1::shared_ptr<DB::AlterConversions const>>>&&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const>, std::__1::shared_ptr<DB::Context const> const&, DB::KeyCondition const&, DB::UsefulSkipIndexes const&, DB::MergeTreeReaderSettings const&, Poco::Logger*, unsigned long, std::__1::vector<DB::ReadFromMergeTree::IndexStat, std::__1::allocator<DB::ReadFromMergeTree::IndexStat>>&, bool)::$_0::operator()(unsigned long) const
    ./build/./src/Storages/MergeTree/MergeTreeDataSelectExecutor.cpp:1017
void std::__1::__function::__policy_invoker<void ()>::__call_impl<std::__1::__function::__default_alloc_func<DB::MergeTreeDataSelectExecutor::filterPartsByPrimaryKeyAndSkipIndexes(std::__1::vector<std::__1::shared_ptr<DB::IMergeTreeDataPart const>, std::__1::allocator<std::__1::shared_ptr<DB::IMergeTreeDataPart const>>>&&, std::__1::vector<std::__1::shared_ptr<DB::AlterConversions const>, std::__1::allocator<std::__1::shared_ptr<DB::AlterConversions const>>>&&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const>, std::__1::shared_ptr<DB::Context const> const&, DB::KeyCondition const&, DB::UsefulSkipIndexes const&, DB::MergeTreeReaderSettings const&, Poco::Logger*, unsigned long, std::__1::vector<DB::ReadFromMergeTree::IndexStat, std::__1::allocator<DB::ReadFromMergeTree::IndexStat>>&, bool)::$_1, void ()>>(std::__1::__function::__policy_storage const*)
    ./build/./src/Storages/MergeTree/MergeTreeDataSelectExecutor.cpp:1090
ThreadPoolImpl<ThreadFromGlobalPoolImpl<false>>::worker(std::__1::__list_iterator<ThreadFromGlobalPoolImpl<false>, void*>)
    ./build/./base/base/../base/wide_integer_impl.h:809
ThreadFromGlobalPoolImpl<false>::ThreadFromGlobalPoolImpl<void ThreadPoolImpl<ThreadFromGlobalPoolImpl<false>>::scheduleImpl<void>(std::__1::function<void ()>, Priority, std::__1::optional<unsigned long>, bool)::'lambda0'()>(void&&)::'lambda'()::operator()()
    ./build/./src/Common/ThreadPool.cpp:0
ThreadPoolImpl<std::__1::thread>::worker(std::__1::__list_iterator<std::__1::thread, void*>)
    ./build/./base/base/../base/wide_integer_impl.h:809
void* std::__1::__thread_proxy[abi:v15000]<std::__1::tuple<std::__1::unique_ptr<std::__1::__thread_struct, std::__1::default_delete<std::__1::__thread_struct>>, void ThreadPoolImpl<std::__1::thread>::scheduleImpl<void>(std::__1::function<void ()>, Priority, std::__1::optional<unsigned long>, bool)::'lambda0'()>>(void*)
    ./build/./contrib/llvm-project/libcxx/include/__memory/unique_ptr.h:302

    

    

Row 7:
──────
count(): 23
sym:     DB::IExecutableFunction::defaultImplementationForConstantArguments(std::__1::vector<DB::ColumnWithTypeAndName, std::__1::allocator<DB::ColumnWithTypeAndName>> const&, std::__1::shared_ptr<DB::IDataType const> const&, unsigned long, bool) const
    ./build/./contrib/boost/boost/smart_ptr/intrusive_ref_counter.hpp:172
DB::IExecutableFunction::executeWithoutLowCardinalityColumns(std::__1::vector<DB::ColumnWithTypeAndName, std::__1::allocator<DB::ColumnWithTypeAndName>> const&, std::__1::shared_ptr<DB::IDataType const> const&, unsigned long, bool) const
    ./build/./src/Functions/IFunction.cpp:0
DB::IExecutableFunction::executeWithoutSparseColumns(std::__1::vector<DB::ColumnWithTypeAndName, std::__1::allocator<DB::ColumnWithTypeAndName>> const&, std::__1::shared_ptr<DB::IDataType const> const&, unsigned long, bool) const
    ./build/./contrib/boost/boost/smart_ptr/intrusive_ptr.hpp:117
DB::IExecutableFunction::execute(std::__1::vector<DB::ColumnWithTypeAndName, std::__1::allocator<DB::ColumnWithTypeAndName>> const&, std::__1::shared_ptr<DB::IDataType const> const&, unsigned long, bool) const
    ./build/./src/Functions/IFunction.cpp:0
DB::FunctionWithOptionalConstArg::execute(std::__1::vector<DB::ColumnWithTypeAndName, std::__1::allocator<DB::ColumnWithTypeAndName>> const&, std::__1::shared_ptr<DB::IDataType const> const&, unsigned long, bool) const
    ./build/./contrib/llvm-project/libcxx/include/__memory/shared_ptr.h:701
DB::applyFunction(std::__1::shared_ptr<DB::IFunctionBase const> const&, std::__1::shared_ptr<DB::IDataType const> const&, DB::FieldRef const&)
    ./build/./contrib/boost/boost/smart_ptr/intrusive_ptr.hpp:196
DB::KeyCondition::applyMonotonicFunctionsChainToRange(DB::Range, std::__1::vector<std::__1::shared_ptr<DB::IFunctionBase const>, std::__1::allocator<std::__1::shared_ptr<DB::IFunctionBase const>>> const&, std::__1::shared_ptr<DB::IDataType const>, bool)
    ./build/./src/Core/Range.h:21
DB::KeyCondition::checkInHyperrectangle(std::__1::vector<DB::Range, std::__1::allocator<DB::Range>> const&, std::__1::vector<std::__1::shared_ptr<DB::IDataType const>, std::__1::allocator<std::__1::shared_ptr<DB::IDataType const>>> const&) const
    ./build/./src/Core/Range.h:43
DB::MergeTreeIndexConditionMinMax::mayBeTrueOnGranule(std::__1::shared_ptr<DB::IMergeTreeIndexGranule>) const
    ./build/./src/Storages/MergeTree/MergeTreeIndexMinMax.cpp:0
DB::MergeTreeDataSelectExecutor::filterMarksUsingIndex(std::__1::shared_ptr<DB::IMergeTreeIndex const>, std::__1::shared_ptr<DB::IMergeTreeIndexCondition>, std::__1::shared_ptr<DB::IMergeTreeDataPart const>, DB::MarkRanges const&, DB::Settings const&, DB::MergeTreeReaderSettings const&, DB::MarkCache*, DB::UncompressedCache*, Poco::Logger*)
    ./build/./src/Storages/MergeTree/MergeTreeDataSelectExecutor.cpp:1704
DB::MergeTreeDataSelectExecutor::filterPartsByPrimaryKeyAndSkipIndexes(std::__1::vector<std::__1::shared_ptr<DB::IMergeTreeDataPart const>, std::__1::allocator<std::__1::shared_ptr<DB::IMergeTreeDataPart const>>>&&, std::__1::vector<std::__1::shared_ptr<DB::AlterConversions const>, std::__1::allocator<std::__1::shared_ptr<DB::AlterConversions const>>>&&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const>, std::__1::shared_ptr<DB::Context const> const&, DB::KeyCondition const&, DB::UsefulSkipIndexes const&, DB::MergeTreeReaderSettings const&, Poco::Logger*, unsigned long, std::__1::vector<DB::ReadFromMergeTree::IndexStat, std::__1::allocator<DB::ReadFromMergeTree::IndexStat>>&, bool)::$_0::operator()(unsigned long) const
    ./build/./src/Storages/MergeTree/MergeTreeDataSelectExecutor.cpp:1017
void std::__1::__function::__policy_invoker<void ()>::__call_impl<std::__1::__function::__default_alloc_func<DB::MergeTreeDataSelectExecutor::filterPartsByPrimaryKeyAndSkipIndexes(std::__1::vector<std::__1::shared_ptr<DB::IMergeTreeDataPart const>, std::__1::allocator<std::__1::shared_ptr<DB::IMergeTreeDataPart const>>>&&, std::__1::vector<std::__1::shared_ptr<DB::AlterConversions const>, std::__1::allocator<std::__1::shared_ptr<DB::AlterConversions const>>>&&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const>, std::__1::shared_ptr<DB::Context const> const&, DB::KeyCondition const&, DB::UsefulSkipIndexes const&, DB::MergeTreeReaderSettings const&, Poco::Logger*, unsigned long, std::__1::vector<DB::ReadFromMergeTree::IndexStat, std::__1::allocator<DB::ReadFromMergeTree::IndexStat>>&, bool)::$_1, void ()>>(std::__1::__function::__policy_storage const*)
    ./build/./src/Storages/MergeTree/MergeTreeDataSelectExecutor.cpp:1090
ThreadPoolImpl<ThreadFromGlobalPoolImpl<false>>::worker(std::__1::__list_iterator<ThreadFromGlobalPoolImpl<false>, void*>)
    ./build/./base/base/../base/wide_integer_impl.h:809
ThreadFromGlobalPoolImpl<false>::ThreadFromGlobalPoolImpl<void ThreadPoolImpl<ThreadFromGlobalPoolImpl<false>>::scheduleImpl<void>(std::__1::function<void ()>, Priority, std::__1::optional<unsigned long>, bool)::'lambda0'()>(void&&)::'lambda'()::operator()()
    ./build/./src/Common/ThreadPool.cpp:0
ThreadPoolImpl<std::__1::thread>::worker(std::__1::__list_iterator<std::__1::thread, void*>)
    ./build/./base/base/../base/wide_integer_impl.h:809
void* std::__1::__thread_proxy[abi:v15000]<std::__1::tuple<std::__1::unique_ptr<std::__1::__thread_struct, std::__1::default_delete<std::__1::__thread_struct>>, void ThreadPoolImpl<std::__1::thread>::scheduleImpl<void>(std::__1::function<void ()>, Priority, std::__1::optional<unsigned long>, bool)::'lambda0'()>>(void*)
    ./build/./contrib/llvm-project/libcxx/include/__memory/unique_ptr.h:302

    

    

Row 8:
──────
count(): 22
sym:     DB::IColumn::convertToFullColumnIfSparse() const
    ./build/./src/Columns/IColumn.h:77
DB::recursiveRemoveSparse(COW<DB::IColumn>::immutable_ptr<DB::IColumn> const&)
    ./build/./src/Columns/ColumnSparse.cpp:0
DB::IExecutableFunction::execute(std::__1::vector<DB::ColumnWithTypeAndName, std::__1::allocator<DB::ColumnWithTypeAndName>> const&, std::__1::shared_ptr<DB::IDataType const> const&, unsigned long, bool) const
    ./build/./contrib/boost/boost/smart_ptr/intrusive_ptr.hpp:117
DB::FunctionWithOptionalConstArg::execute(std::__1::vector<DB::ColumnWithTypeAndName, std::__1::allocator<DB::ColumnWithTypeAndName>> const&, std::__1::shared_ptr<DB::IDataType const> const&, unsigned long, bool) const
    ./build/./contrib/llvm-project/libcxx/include/__memory/shared_ptr.h:701
DB::applyFunction(std::__1::shared_ptr<DB::IFunctionBase const> const&, std::__1::shared_ptr<DB::IDataType const> const&, DB::FieldRef const&)
    ./build/./contrib/boost/boost/smart_ptr/intrusive_ptr.hpp:196
DB::KeyCondition::applyMonotonicFunctionsChainToRange(DB::Range, std::__1::vector<std::__1::shared_ptr<DB::IFunctionBase const>, std::__1::allocator<std::__1::shared_ptr<DB::IFunctionBase const>>> const&, std::__1::shared_ptr<DB::IDataType const>, bool)
    ./build/./src/Core/Range.h:21
DB::KeyCondition::checkInHyperrectangle(std::__1::vector<DB::Range, std::__1::allocator<DB::Range>> const&, std::__1::vector<std::__1::shared_ptr<DB::IDataType const>, std::__1::allocator<std::__1::shared_ptr<DB::IDataType const>>> const&) const
    ./build/./src/Core/Range.h:43
DB::MergeTreeIndexConditionMinMax::mayBeTrueOnGranule(std::__1::shared_ptr<DB::IMergeTreeIndexGranule>) const
    ./build/./src/Storages/MergeTree/MergeTreeIndexMinMax.cpp:0
DB::MergeTreeDataSelectExecutor::filterMarksUsingIndex(std::__1::shared_ptr<DB::IMergeTreeIndex const>, std::__1::shared_ptr<DB::IMergeTreeIndexCondition>, std::__1::shared_ptr<DB::IMergeTreeDataPart const>, DB::MarkRanges const&, DB::Settings const&, DB::MergeTreeReaderSettings const&, DB::MarkCache*, DB::UncompressedCache*, Poco::Logger*)
    ./build/./src/Storages/MergeTree/MergeTreeDataSelectExecutor.cpp:1704
DB::MergeTreeDataSelectExecutor::filterPartsByPrimaryKeyAndSkipIndexes(std::__1::vector<std::__1::shared_ptr<DB::IMergeTreeDataPart const>, std::__1::allocator<std::__1::shared_ptr<DB::IMergeTreeDataPart const>>>&&, std::__1::vector<std::__1::shared_ptr<DB::AlterConversions const>, std::__1::allocator<std::__1::shared_ptr<DB::AlterConversions const>>>&&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const>, std::__1::shared_ptr<DB::Context const> const&, DB::KeyCondition const&, DB::UsefulSkipIndexes const&, DB::MergeTreeReaderSettings const&, Poco::Logger*, unsigned long, std::__1::vector<DB::ReadFromMergeTree::IndexStat, std::__1::allocator<DB::ReadFromMergeTree::IndexStat>>&, bool)::$_0::operator()(unsigned long) const
    ./build/./src/Storages/MergeTree/MergeTreeDataSelectExecutor.cpp:1017
void std::__1::__function::__policy_invoker<void ()>::__call_impl<std::__1::__function::__default_alloc_func<DB::MergeTreeDataSelectExecutor::filterPartsByPrimaryKeyAndSkipIndexes(std::__1::vector<std::__1::shared_ptr<DB::IMergeTreeDataPart const>, std::__1::allocator<std::__1::shared_ptr<DB::IMergeTreeDataPart const>>>&&, std::__1::vector<std::__1::shared_ptr<DB::AlterConversions const>, std::__1::allocator<std::__1::shared_ptr<DB::AlterConversions const>>>&&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const>, std::__1::shared_ptr<DB::Context const> const&, DB::KeyCondition const&, DB::UsefulSkipIndexes const&, DB::MergeTreeReaderSettings const&, Poco::Logger*, unsigned long, std::__1::vector<DB::ReadFromMergeTree::IndexStat, std::__1::allocator<DB::ReadFromMergeTree::IndexStat>>&, bool)::$_1, void ()>>(std::__1::__function::__policy_storage const*)
    ./build/./src/Storages/MergeTree/MergeTreeDataSelectExecutor.cpp:1090
ThreadPoolImpl<ThreadFromGlobalPoolImpl<false>>::worker(std::__1::__list_iterator<ThreadFromGlobalPoolImpl<false>, void*>)
    ./build/./base/base/../base/wide_integer_impl.h:809
ThreadFromGlobalPoolImpl<false>::ThreadFromGlobalPoolImpl<void ThreadPoolImpl<ThreadFromGlobalPoolImpl<false>>::scheduleImpl<void>(std::__1::function<void ()>, Priority, std::__1::optional<unsigned long>, bool)::'lambda0'()>(void&&)::'lambda'()::operator()()
    ./build/./src/Common/ThreadPool.cpp:0
ThreadPoolImpl<std::__1::thread>::worker(std::__1::__list_iterator<std::__1::thread, void*>)
    ./build/./base/base/../base/wide_integer_impl.h:809
void* std::__1::__thread_proxy[abi:v15000]<std::__1::tuple<std::__1::unique_ptr<std::__1::__thread_struct, std::__1::default_delete<std::__1::__thread_struct>>, void ThreadPoolImpl<std::__1::thread>::scheduleImpl<void>(std::__1::function<void ()>, Priority, std::__1::optional<unsigned long>, bool)::'lambda0'()>>(void*)
    ./build/./contrib/llvm-project/libcxx/include/__memory/unique_ptr.h:302

    

    

Row 9:
───────
count(): 22
sym:     DB::ColumnWithTypeAndName* std::__1::construct_at[abi:v15000]<DB::ColumnWithTypeAndName, DB::ColumnWithTypeAndName&, DB::ColumnWithTypeAndName*>(DB::ColumnWithTypeAndName*, DB::ColumnWithTypeAndName&)
    /home/milovidov/work/ClickHouse/build/programs/clickhouse
DB::IExecutableFunction::execute(std::__1::vector<DB::ColumnWithTypeAndName, std::__1::allocator<DB::ColumnWithTypeAndName>> const&, std::__1::shared_ptr<DB::IDataType const> const&, unsigned long, bool) const
    ./build/./contrib/llvm-project/libcxx/include/__memory/uninitialized_algorithms.h:539
DB::FunctionWithOptionalConstArg::execute(std::__1::vector<DB::ColumnWithTypeAndName, std::__1::allocator<DB::ColumnWithTypeAndName>> const&, std::__1::shared_ptr<DB::IDataType const> const&, unsigned long, bool) const
    ./build/./contrib/llvm-project/libcxx/include/__memory/shared_ptr.h:701
DB::applyFunction(std::__1::shared_ptr<DB::IFunctionBase const> const&, std::__1::shared_ptr<DB::IDataType const> const&, DB::FieldRef const&)
    ./build/./contrib/boost/boost/smart_ptr/intrusive_ptr.hpp:196
DB::KeyCondition::applyMonotonicFunctionsChainToRange(DB::Range, std::__1::vector<std::__1::shared_ptr<DB::IFunctionBase const>, std::__1::allocator<std::__1::shared_ptr<DB::IFunctionBase const>>> const&, std::__1::shared_ptr<DB::IDataType const>, bool)
    ./build/./src/Core/Range.h:21
DB::KeyCondition::checkInHyperrectangle(std::__1::vector<DB::Range, std::__1::allocator<DB::Range>> const&, std::__1::vector<std::__1::shared_ptr<DB::IDataType const>, std::__1::allocator<std::__1::shared_ptr<DB::IDataType const>>> const&) const
    ./build/./src/Core/Range.h:43
DB::MergeTreeIndexConditionMinMax::mayBeTrueOnGranule(std::__1::shared_ptr<DB::IMergeTreeIndexGranule>) const
    ./build/./src/Storages/MergeTree/MergeTreeIndexMinMax.cpp:0
DB::MergeTreeDataSelectExecutor::filterMarksUsingIndex(std::__1::shared_ptr<DB::IMergeTreeIndex const>, std::__1::shared_ptr<DB::IMergeTreeIndexCondition>, std::__1::shared_ptr<DB::IMergeTreeDataPart const>, DB::MarkRanges const&, DB::Settings const&, DB::MergeTreeReaderSettings const&, DB::MarkCache*, DB::UncompressedCache*, Poco::Logger*)
    ./build/./src/Storages/MergeTree/MergeTreeDataSelectExecutor.cpp:1704
DB::MergeTreeDataSelectExecutor::filterPartsByPrimaryKeyAndSkipIndexes(std::__1::vector<std::__1::shared_ptr<DB::IMergeTreeDataPart const>, std::__1::allocator<std::__1::shared_ptr<DB::IMergeTreeDataPart const>>>&&, std::__1::vector<std::__1::shared_ptr<DB::AlterConversions const>, std::__1::allocator<std::__1::shared_ptr<DB::AlterConversions const>>>&&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const>, std::__1::shared_ptr<DB::Context const> const&, DB::KeyCondition const&, DB::UsefulSkipIndexes const&, DB::MergeTreeReaderSettings const&, Poco::Logger*, unsigned long, std::__1::vector<DB::ReadFromMergeTree::IndexStat, std::__1::allocator<DB::ReadFromMergeTree::IndexStat>>&, bool)::$_0::operator()(unsigned long) const
    ./build/./src/Storages/MergeTree/MergeTreeDataSelectExecutor.cpp:1017
void std::__1::__function::__policy_invoker<void ()>::__call_impl<std::__1::__function::__default_alloc_func<DB::MergeTreeDataSelectExecutor::filterPartsByPrimaryKeyAndSkipIndexes(std::__1::vector<std::__1::shared_ptr<DB::IMergeTreeDataPart const>, std::__1::allocator<std::__1::shared_ptr<DB::IMergeTreeDataPart const>>>&&, std::__1::vector<std::__1::shared_ptr<DB::AlterConversions const>, std::__1::allocator<std::__1::shared_ptr<DB::AlterConversions const>>>&&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const>, std::__1::shared_ptr<DB::Context const> const&, DB::KeyCondition const&, DB::UsefulSkipIndexes const&, DB::MergeTreeReaderSettings const&, Poco::Logger*, unsigned long, std::__1::vector<DB::ReadFromMergeTree::IndexStat, std::__1::allocator<DB::ReadFromMergeTree::IndexStat>>&, bool)::$_1, void ()>>(std::__1::__function::__policy_storage const*)
    ./build/./src/Storages/MergeTree/MergeTreeDataSelectExecutor.cpp:1090
ThreadPoolImpl<ThreadFromGlobalPoolImpl<false>>::worker(std::__1::__list_iterator<ThreadFromGlobalPoolImpl<false>, void*>)
    ./build/./base/base/../base/wide_integer_impl.h:809
ThreadFromGlobalPoolImpl<false>::ThreadFromGlobalPoolImpl<void ThreadPoolImpl<ThreadFromGlobalPoolImpl<false>>::scheduleImpl<void>(std::__1::function<void ()>, Priority, std::__1::optional<unsigned long>, bool)::'lambda0'()>(void&&)::'lambda'()::operator()()
    ./build/./src/Common/ThreadPool.cpp:0
ThreadPoolImpl<std::__1::thread>::worker(std::__1::__list_iterator<std::__1::thread, void*>)
    ./build/./base/base/../base/wide_integer_impl.h:809
void* std::__1::__thread_proxy[abi:v15000]<std::__1::tuple<std::__1::unique_ptr<std::__1::__thread_struct, std::__1::default_delete<std::__1::__thread_struct>>, void ThreadPoolImpl<std::__1::thread>::scheduleImpl<void>(std::__1::function<void ()>, Priority, std::__1::optional<unsigned long>, bool)::'lambda0'()>>(void*)
    ./build/./contrib/llvm-project/libcxx/include/__memory/unique_ptr.h:302

    

    

Row 10:
───────
count(): 21
sym:     nallocx
    ./build/./contrib/jemalloc/src/jemalloc.c:4028

@alexey-milovidov
Copy link
Member Author

It spends time applying the function _CAST.

@alexey-milovidov
Copy link
Member Author

It happens because _CAST is implicitly created for ALIAS columns.

If I substitute aliases manually:

SELECT count() FROM index_test 
WHERE mortonDecode(2, z).1 >= 20000 
  AND mortonDecode(2, z).1 <= 20100 
  AND mortonDecode(2, z).2 >= 10000 
  AND mortonDecode(2, z).2 <= 10100

it takes 163 ms, which is also slow, but not as much.

@alexey-milovidov
Copy link
Member Author

Strange, it looks like the mark cache is ineffective for index:

Row 1:
──────
count(): 751
sym:     StackTrace::StackTrace(ucontext_t const&)
    ./build/./src/Common/StackTrace.cpp:286
DB::(anonymous namespace)::writeTraceInfo(DB::TraceType, int, siginfo_t*, void*)
    ./build/./src/Common/QueryProfiler.cpp:76

    

Row 2:
──────
count(): 186
sym:     DB::Field::~Field()
    /home/milovidov/work/ClickHouse/build/programs/clickhouse

Row 3:
──────
count(): 175
sym:     ZSTD_decompressSequences_bmi2
    ./build/./contrib/zstd/lib/decompress/../common/zstd_internal.h:178
ZSTD_decompressBlock_internal
    ./build/./contrib/zstd/lib/decompress/zstd_decompress_block.c:0
ZSTD_decompressMultiFrame
    ./build/./contrib/zstd/lib/decompress/zstd_decompress.c:0
ZSTD_decompress
    ./build/./contrib/zstd/lib/decompress/zstd_decompress.c:1634
DB::CompressionCodecZSTD::doDecompressData(char const*, unsigned int, char*, unsigned int) const
    ./build/./src/Compression/CompressionCodecZSTD.cpp:93
DB::ICompressionCodec::decompress(char const*, unsigned int, char*) const
    ./build/./src/Compression/ICompressionCodec.cpp:112
DB::CachedCompressedReadBuffer::nextImpl()
    ./build/./contrib/llvm-project/libcxx/include/__utility/swap.h:37
DB::ReadBuffer::readStrict(char*, unsigned long)
    /home/milovidov/work/ClickHouse/build/programs/clickhouse
DB::SerializationNumber<unsigned long>::deserializeBinary(DB::Field&, DB::ReadBuffer&, DB::FormatSettings const&) const
    ./build/./src/DataTypes/Serializations/SerializationNumber.cpp:119
DB::MergeTreeIndexGranuleMinMax::deserializeBinary(DB::ReadBuffer&, unsigned char)
    ./build/./src/Storages/MergeTree/MergeTreeIndexMinMax.cpp:93
DB::MergeTreeIndexReader::read()
    ./build/./src/Storages/MergeTree/MergeTreeIndexReader.cpp:77
DB::MergeTreeDataSelectExecutor::filterMarksUsingIndex(std::__1::shared_ptr<DB::IMergeTreeIndex const>, std::__1::shared_ptr<DB::IMergeTreeIndexCondition>, std::__1::shared_ptr<DB::IMergeTreeDataPart const>, DB::MarkRanges const&, DB::Settings const&, DB::MergeTreeReaderSettings const&, DB::MarkCache*, DB::UncompressedCache*, Poco::Logger*)
    ./build/./contrib/llvm-project/libcxx/include/__utility/swap.h:37
DB::MergeTreeDataSelectExecutor::filterPartsByPrimaryKeyAndSkipIndexes(std::__1::vector<std::__1::shared_ptr<DB::IMergeTreeDataPart const>, std::__1::allocator<std::__1::shared_ptr<DB::IMergeTreeDataPart const>>>&&, std::__1::vector<std::__1::shared_ptr<DB::AlterConversions const>, std::__1::allocator<std::__1::shared_ptr<DB::AlterConversions const>>>&&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const>, std::__1::shared_ptr<DB::Context const> const&, DB::KeyCondition const&, DB::UsefulSkipIndexes const&, DB::MergeTreeReaderSettings const&, Poco::Logger*, unsigned long, std::__1::vector<DB::ReadFromMergeTree::IndexStat, std::__1::allocator<DB::ReadFromMergeTree::IndexStat>>&, bool)::$_0::operator()(unsigned long) const
    ./build/./src/Storages/MergeTree/MergeTreeDataSelectExecutor.cpp:1017
void std::__1::__function::__policy_invoker<void ()>::__call_impl<std::__1::__function::__default_alloc_func<DB::MergeTreeDataSelectExecutor::filterPartsByPrimaryKeyAndSkipIndexes(std::__1::vector<std::__1::shared_ptr<DB::IMergeTreeDataPart const>, std::__1::allocator<std::__1::shared_ptr<DB::IMergeTreeDataPart const>>>&&, std::__1::vector<std::__1::shared_ptr<DB::AlterConversions const>, std::__1::allocator<std::__1::shared_ptr<DB::AlterConversions const>>>&&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const>, std::__1::shared_ptr<DB::Context const> const&, DB::KeyCondition const&, DB::UsefulSkipIndexes const&, DB::MergeTreeReaderSettings const&, Poco::Logger*, unsigned long, std::__1::vector<DB::ReadFromMergeTree::IndexStat, std::__1::allocator<DB::ReadFromMergeTree::IndexStat>>&, bool)::$_1, void ()>>(std::__1::__function::__policy_storage const*)
    ./build/./src/Storages/MergeTree/MergeTreeDataSelectExecutor.cpp:1090
ThreadPoolImpl<ThreadFromGlobalPoolImpl<false>>::worker(std::__1::__list_iterator<ThreadFromGlobalPoolImpl<false>, void*>)
    ./build/./base/base/../base/wide_integer_impl.h:809
ThreadFromGlobalPoolImpl<false>::ThreadFromGlobalPoolImpl<void ThreadPoolImpl<ThreadFromGlobalPoolImpl<false>>::scheduleImpl<void>(std::__1::function<void ()>, Priority, std::__1::optional<unsigned long>, bool)::'lambda0'()>(void&&)::'lambda'()::operator()()
    ./build/./src/Common/ThreadPool.cpp:0
ThreadPoolImpl<std::__1::thread>::worker(std::__1::__list_iterator<std::__1::thread, void*>)
    ./build/./base/base/../base/wide_integer_impl.h:809
void* std::__1::__thread_proxy[abi:v15000]<std::__1::tuple<std::__1::unique_ptr<std::__1::__thread_struct, std::__1::default_delete<std::__1::__thread_struct>>, void ThreadPoolImpl<std::__1::thread>::scheduleImpl<void>(std::__1::function<void ()>, Priority, std::__1::optional<unsigned long>, bool)::'lambda0'()>>(void*)
    ./build/./contrib/llvm-project/libcxx/include/__memory/unique_ptr.h:302

    

    

Row 4:
──────
count(): 145
sym:     memcpy
    ./build/./base/glibc-compatibility/memcpy/memcpy.h:121
DB::ReadBuffer::readStrict(char*, unsigned long)
    /home/milovidov/work/ClickHouse/build/programs/clickhouse
DB::MergeTreeMarksLoader::loadMarksImpl()
    ./build/./src/IO/ReadHelpers.h:116
DB::MergeTreeMarksLoader::loadMarks()
    ./build/./contrib/llvm-project/libcxx/include/__utility/swap.h:37
DB::MergeTreeMarksLoader::getMark(unsigned long, unsigned long)
    ./build/./contrib/llvm-project/libcxx/include/__utility/swap.h:37
DB::MergeTreeReaderStream::init()
    ./build/./src/Storages/MergeTree/MergeTreeReaderStream.cpp:72
DB::MergeTreeReaderStream::adjustRightMark(unsigned long)
    ./build/./src/Storages/MergeTree/MergeTreeReaderStream.cpp:276
DB::MergeTreeIndexReader::MergeTreeIndexReader(std::__1::shared_ptr<DB::IMergeTreeIndex const>, std::__1::shared_ptr<DB::IMergeTreeDataPart const>, unsigned long, DB::MarkRanges const&, DB::MarkCache*, DB::UncompressedCache*, DB::MergeTreeReaderSettings)
    ./build/./contrib/llvm-project/libcxx/include/__memory/unique_ptr.h:274
DB::MergeTreeDataSelectExecutor::filterMarksUsingIndex(std::__1::shared_ptr<DB::IMergeTreeIndex const>, std::__1::shared_ptr<DB::IMergeTreeIndexCondition>, std::__1::shared_ptr<DB::IMergeTreeDataPart const>, DB::MarkRanges const&, DB::Settings const&, DB::MergeTreeReaderSettings const&, DB::MarkCache*, DB::UncompressedCache*, Poco::Logger*)
    ./build/./contrib/llvm-project/libcxx/include/deque:1188
DB::MergeTreeDataSelectExecutor::filterPartsByPrimaryKeyAndSkipIndexes(std::__1::vector<std::__1::shared_ptr<DB::IMergeTreeDataPart const>, std::__1::allocator<std::__1::shared_ptr<DB::IMergeTreeDataPart const>>>&&, std::__1::vector<std::__1::shared_ptr<DB::AlterConversions const>, std::__1::allocator<std::__1::shared_ptr<DB::AlterConversions const>>>&&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const>, std::__1::shared_ptr<DB::Context const> const&, DB::KeyCondition const&, DB::UsefulSkipIndexes const&, DB::MergeTreeReaderSettings const&, Poco::Logger*, unsigned long, std::__1::vector<DB::ReadFromMergeTree::IndexStat, std::__1::allocator<DB::ReadFromMergeTree::IndexStat>>&, bool)::$_0::operator()(unsigned long) const
    ./build/./src/Storages/MergeTree/MergeTreeDataSelectExecutor.cpp:1017
void std::__1::__function::__policy_invoker<void ()>::__call_impl<std::__1::__function::__default_alloc_func<DB::MergeTreeDataSelectExecutor::filterPartsByPrimaryKeyAndSkipIndexes(std::__1::vector<std::__1::shared_ptr<DB::IMergeTreeDataPart const>, std::__1::allocator<std::__1::shared_ptr<DB::IMergeTreeDataPart const>>>&&, std::__1::vector<std::__1::shared_ptr<DB::AlterConversions const>, std::__1::allocator<std::__1::shared_ptr<DB::AlterConversions const>>>&&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const>, std::__1::shared_ptr<DB::Context const> const&, DB::KeyCondition const&, DB::UsefulSkipIndexes const&, DB::MergeTreeReaderSettings const&, Poco::Logger*, unsigned long, std::__1::vector<DB::ReadFromMergeTree::IndexStat, std::__1::allocator<DB::ReadFromMergeTree::IndexStat>>&, bool)::$_1, void ()>>(std::__1::__function::__policy_storage const*)
    ./build/./src/Storages/MergeTree/MergeTreeDataSelectExecutor.cpp:1090
ThreadPoolImpl<ThreadFromGlobalPoolImpl<false>>::worker(std::__1::__list_iterator<ThreadFromGlobalPoolImpl<false>, void*>)
    ./build/./base/base/../base/wide_integer_impl.h:809
ThreadFromGlobalPoolImpl<false>::ThreadFromGlobalPoolImpl<void ThreadPoolImpl<ThreadFromGlobalPoolImpl<false>>::scheduleImpl<void>(std::__1::function<void ()>, Priority, std::__1::optional<unsigned long>, bool)::'lambda0'()>(void&&)::'lambda'()::operator()()
    ./build/./src/Common/ThreadPool.cpp:0
ThreadPoolImpl<std::__1::thread>::worker(std::__1::__list_iterator<std::__1::thread, void*>)
    ./build/./base/base/../base/wide_integer_impl.h:809
void* std::__1::__thread_proxy[abi:v15000]<std::__1::tuple<std::__1::unique_ptr<std::__1::__thread_struct, std::__1::default_delete<std::__1::__thread_struct>>, void ThreadPoolImpl<std::__1::thread>::scheduleImpl<void>(std::__1::function<void ()>, Priority, std::__1::optional<unsigned long>, bool)::'lambda0'()>>(void*)
    ./build/./contrib/llvm-project/libcxx/include/__memory/unique_ptr.h:302

    

    

Row 5:
──────
count(): 122
sym:     ZSTD_decompressSequences_bmi2
    ./build/./contrib/zstd/lib/decompress/../common/zstd_internal.h:191
ZSTD_decompressBlock_internal
    ./build/./contrib/zstd/lib/decompress/zstd_decompress_block.c:0
ZSTD_decompressMultiFrame
    ./build/./contrib/zstd/lib/decompress/zstd_decompress.c:0
ZSTD_decompress
    ./build/./contrib/zstd/lib/decompress/zstd_decompress.c:1634
DB::CompressionCodecZSTD::doDecompressData(char const*, unsigned int, char*, unsigned int) const
    ./build/./src/Compression/CompressionCodecZSTD.cpp:93
DB::ICompressionCodec::decompress(char const*, unsigned int, char*) const
    ./build/./src/Compression/ICompressionCodec.cpp:112
DB::CompressedReadBufferFromFile::nextImpl()
    ./build/./src/IO/BufferBase.h:41
DB::MergeTreeMarksLoader::loadMarksImpl()
    ./build/./src/IO/ReadBuffer.h:70
DB::MergeTreeMarksLoader::loadMarks()
    ./build/./contrib/llvm-project/libcxx/include/__utility/swap.h:37
DB::MergeTreeMarksLoader::getMark(unsigned long, unsigned long)
    ./build/./contrib/llvm-project/libcxx/include/__utility/swap.h:37
DB::MergeTreeReaderStream::init()
    ./build/./src/Storages/MergeTree/MergeTreeReaderStream.cpp:72
DB::MergeTreeReaderStream::adjustRightMark(unsigned long)
    ./build/./src/Storages/MergeTree/MergeTreeReaderStream.cpp:276
DB::MergeTreeIndexReader::MergeTreeIndexReader(std::__1::shared_ptr<DB::IMergeTreeIndex const>, std::__1::shared_ptr<DB::IMergeTreeDataPart const>, unsigned long, DB::MarkRanges const&, DB::MarkCache*, DB::UncompressedCache*, DB::MergeTreeReaderSettings)
    ./build/./contrib/llvm-project/libcxx/include/__memory/unique_ptr.h:274
DB::MergeTreeDataSelectExecutor::filterMarksUsingIndex(std::__1::shared_ptr<DB::IMergeTreeIndex const>, std::__1::shared_ptr<DB::IMergeTreeIndexCondition>, std::__1::shared_ptr<DB::IMergeTreeDataPart const>, DB::MarkRanges const&, DB::Settings const&, DB::MergeTreeReaderSettings const&, DB::MarkCache*, DB::UncompressedCache*, Poco::Logger*)
    ./build/./contrib/llvm-project/libcxx/include/deque:1188
DB::MergeTreeDataSelectExecutor::filterPartsByPrimaryKeyAndSkipIndexes(std::__1::vector<std::__1::shared_ptr<DB::IMergeTreeDataPart const>, std::__1::allocator<std::__1::shared_ptr<DB::IMergeTreeDataPart const>>>&&, std::__1::vector<std::__1::shared_ptr<DB::AlterConversions const>, std::__1::allocator<std::__1::shared_ptr<DB::AlterConversions const>>>&&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const>, std::__1::shared_ptr<DB::Context const> const&, DB::KeyCondition const&, DB::UsefulSkipIndexes const&, DB::MergeTreeReaderSettings const&, Poco::Logger*, unsigned long, std::__1::vector<DB::ReadFromMergeTree::IndexStat, std::__1::allocator<DB::ReadFromMergeTree::IndexStat>>&, bool)::$_0::operator()(unsigned long) const
    ./build/./src/Storages/MergeTree/MergeTreeDataSelectExecutor.cpp:1017
void std::__1::__function::__policy_invoker<void ()>::__call_impl<std::__1::__function::__default_alloc_func<DB::MergeTreeDataSelectExecutor::filterPartsByPrimaryKeyAndSkipIndexes(std::__1::vector<std::__1::shared_ptr<DB::IMergeTreeDataPart const>, std::__1::allocator<std::__1::shared_ptr<DB::IMergeTreeDataPart const>>>&&, std::__1::vector<std::__1::shared_ptr<DB::AlterConversions const>, std::__1::allocator<std::__1::shared_ptr<DB::AlterConversions const>>>&&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const>, std::__1::shared_ptr<DB::Context const> const&, DB::KeyCondition const&, DB::UsefulSkipIndexes const&, DB::MergeTreeReaderSettings const&, Poco::Logger*, unsigned long, std::__1::vector<DB::ReadFromMergeTree::IndexStat, std::__1::allocator<DB::ReadFromMergeTree::IndexStat>>&, bool)::$_1, void ()>>(std::__1::__function::__policy_storage const*)
    ./build/./src/Storages/MergeTree/MergeTreeDataSelectExecutor.cpp:1090
ThreadPoolImpl<ThreadFromGlobalPoolImpl<false>>::worker(std::__1::__list_iterator<ThreadFromGlobalPoolImpl<false>, void*>)
    ./build/./base/base/../base/wide_integer_impl.h:809
ThreadFromGlobalPoolImpl<false>::ThreadFromGlobalPoolImpl<void ThreadPoolImpl<ThreadFromGlobalPoolImpl<false>>::scheduleImpl<void>(std::__1::function<void ()>, Priority, std::__1::optional<unsigned long>, bool)::'lambda0'()>(void&&)::'lambda'()::operator()()
    ./build/./src/Common/ThreadPool.cpp:0
ThreadPoolImpl<std::__1::thread>::worker(std::__1::__list_iterator<std::__1::thread, void*>)
    ./build/./base/base/../base/wide_integer_impl.h:809
void* std::__1::__thread_proxy[abi:v15000]<std::__1::tuple<std::__1::unique_ptr<std::__1::__thread_struct, std::__1::default_delete<std::__1::__thread_struct>>, void ThreadPoolImpl<std::__1::thread>::scheduleImpl<void>(std::__1::function<void ()>, Priority, std::__1::optional<unsigned long>, bool)::'lambda0'()>>(void*)
    ./build/./contrib/llvm-project/libcxx/include/__memory/unique_ptr.h:302

    

    

Row 6:
──────
count(): 115
sym:     ZSTD_decompressSequences_bmi2
    ./build/./contrib/zstd/lib/decompress/../common/zstd_internal.h:191
ZSTD_decompressBlock_internal
    ./build/./contrib/zstd/lib/decompress/zstd_decompress_block.c:0
ZSTD_decompressMultiFrame
    ./build/./contrib/zstd/lib/decompress/zstd_decompress.c:0
ZSTD_decompress
    ./build/./contrib/zstd/lib/decompress/zstd_decompress.c:1634
DB::CompressionCodecZSTD::doDecompressData(char const*, unsigned int, char*, unsigned int) const
    ./build/./src/Compression/CompressionCodecZSTD.cpp:93
DB::ICompressionCodec::decompress(char const*, unsigned int, char*) const
    ./build/./src/Compression/ICompressionCodec.cpp:112
DB::CompressedReadBufferFromFile::nextImpl()
    ./build/./src/IO/BufferBase.h:41
DB::ReadBuffer::readStrict(char*, unsigned long)
    /home/milovidov/work/ClickHouse/build/programs/clickhouse
DB::MergeTreeMarksLoader::loadMarksImpl()
    ./build/./src/Storages/MergeTree/MergeTreeMarksLoader.cpp:153
DB::MergeTreeMarksLoader::loadMarks()
    ./build/./contrib/llvm-project/libcxx/include/__utility/swap.h:37
DB::MergeTreeMarksLoader::getMark(unsigned long, unsigned long)
    ./build/./contrib/llvm-project/libcxx/include/__utility/swap.h:37
DB::MergeTreeReaderStream::init()
    ./build/./src/Storages/MergeTree/MergeTreeReaderStream.cpp:72
DB::MergeTreeReaderStream::adjustRightMark(unsigned long)
    ./build/./src/Storages/MergeTree/MergeTreeReaderStream.cpp:276
DB::MergeTreeIndexReader::MergeTreeIndexReader(std::__1::shared_ptr<DB::IMergeTreeIndex const>, std::__1::shared_ptr<DB::IMergeTreeDataPart const>, unsigned long, DB::MarkRanges const&, DB::MarkCache*, DB::UncompressedCache*, DB::MergeTreeReaderSettings)
    ./build/./contrib/llvm-project/libcxx/include/__memory/unique_ptr.h:274
DB::MergeTreeDataSelectExecutor::filterMarksUsingIndex(std::__1::shared_ptr<DB::IMergeTreeIndex const>, std::__1::shared_ptr<DB::IMergeTreeIndexCondition>, std::__1::shared_ptr<DB::IMergeTreeDataPart const>, DB::MarkRanges const&, DB::Settings const&, DB::MergeTreeReaderSettings const&, DB::MarkCache*, DB::UncompressedCache*, Poco::Logger*)
    ./build/./contrib/llvm-project/libcxx/include/deque:1188
DB::MergeTreeDataSelectExecutor::filterPartsByPrimaryKeyAndSkipIndexes(std::__1::vector<std::__1::shared_ptr<DB::IMergeTreeDataPart const>, std::__1::allocator<std::__1::shared_ptr<DB::IMergeTreeDataPart const>>>&&, std::__1::vector<std::__1::shared_ptr<DB::AlterConversions const>, std::__1::allocator<std::__1::shared_ptr<DB::AlterConversions const>>>&&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const>, std::__1::shared_ptr<DB::Context const> const&, DB::KeyCondition const&, DB::UsefulSkipIndexes const&, DB::MergeTreeReaderSettings const&, Poco::Logger*, unsigned long, std::__1::vector<DB::ReadFromMergeTree::IndexStat, std::__1::allocator<DB::ReadFromMergeTree::IndexStat>>&, bool)::$_0::operator()(unsigned long) const
    ./build/./src/Storages/MergeTree/MergeTreeDataSelectExecutor.cpp:1017
void std::__1::__function::__policy_invoker<void ()>::__call_impl<std::__1::__function::__default_alloc_func<DB::MergeTreeDataSelectExecutor::filterPartsByPrimaryKeyAndSkipIndexes(std::__1::vector<std::__1::shared_ptr<DB::IMergeTreeDataPart const>, std::__1::allocator<std::__1::shared_ptr<DB::IMergeTreeDataPart const>>>&&, std::__1::vector<std::__1::shared_ptr<DB::AlterConversions const>, std::__1::allocator<std::__1::shared_ptr<DB::AlterConversions const>>>&&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const>, std::__1::shared_ptr<DB::Context const> const&, DB::KeyCondition const&, DB::UsefulSkipIndexes const&, DB::MergeTreeReaderSettings const&, Poco::Logger*, unsigned long, std::__1::vector<DB::ReadFromMergeTree::IndexStat, std::__1::allocator<DB::ReadFromMergeTree::IndexStat>>&, bool)::$_1, void ()>>(std::__1::__function::__policy_storage const*)
    ./build/./src/Storages/MergeTree/MergeTreeDataSelectExecutor.cpp:1090
ThreadPoolImpl<ThreadFromGlobalPoolImpl<false>>::worker(std::__1::__list_iterator<ThreadFromGlobalPoolImpl<false>, void*>)
    ./build/./base/base/../base/wide_integer_impl.h:809
ThreadFromGlobalPoolImpl<false>::ThreadFromGlobalPoolImpl<void ThreadPoolImpl<ThreadFromGlobalPoolImpl<false>>::scheduleImpl<void>(std::__1::function<void ()>, Priority, std::__1::optional<unsigned long>, bool)::'lambda0'()>(void&&)::'lambda'()::operator()()
    ./build/./src/Common/ThreadPool.cpp:0
ThreadPoolImpl<std::__1::thread>::worker(std::__1::__list_iterator<std::__1::thread, void*>)
    ./build/./base/base/../base/wide_integer_impl.h:809
void* std::__1::__thread_proxy[abi:v15000]<std::__1::tuple<std::__1::unique_ptr<std::__1::__thread_struct, std::__1::default_delete<std::__1::__thread_struct>>, void ThreadPoolImpl<std::__1::thread>::scheduleImpl<void>(std::__1::function<void ()>, Priority, std::__1::optional<unsigned long>, bool)::'lambda0'()>>(void*)
    ./build/./contrib/llvm-project/libcxx/include/__memory/unique_ptr.h:302

    

    

Row 7:
──────
count(): 105
sym:     auto DB::Field::dispatch<auto DB::applyVisitor<DB::FieldVisitorAccurateLess, DB::Field const&, DB::Field const&>(DB::FieldVisitorAccurateLess&&, DB::Field const&, DB::Field const&)::'lambda'(DB::FieldVisitorAccurateLess&), DB::Field const&>(DB::FieldVisitorAccurateLess&&, DB::Field const&)
    /home/milovidov/work/ClickHouse/build/programs/clickhouse

Row 8:
──────
count(): 103
sym:     DB::MergeTreeIndexConditionMinMax::mayBeTrueOnGranule(std::__1::shared_ptr<DB::IMergeTreeIndexGranule>) const
    ./build/./contrib/llvm-project/libcxx/include/__memory/shared_ptr.h:173
DB::MergeTreeDataSelectExecutor::filterMarksUsingIndex(std::__1::shared_ptr<DB::IMergeTreeIndex const>, std::__1::shared_ptr<DB::IMergeTreeIndexCondition>, std::__1::shared_ptr<DB::IMergeTreeDataPart const>, DB::MarkRanges const&, DB::Settings const&, DB::MergeTreeReaderSettings const&, DB::MarkCache*, DB::UncompressedCache*, Poco::Logger*)
    ./build/./src/Storages/MergeTree/MergeTreeDataSelectExecutor.cpp:1704
DB::MergeTreeDataSelectExecutor::filterPartsByPrimaryKeyAndSkipIndexes(std::__1::vector<std::__1::shared_ptr<DB::IMergeTreeDataPart const>, std::__1::allocator<std::__1::shared_ptr<DB::IMergeTreeDataPart const>>>&&, std::__1::vector<std::__1::shared_ptr<DB::AlterConversions const>, std::__1::allocator<std::__1::shared_ptr<DB::AlterConversions const>>>&&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const>, std::__1::shared_ptr<DB::Context const> const&, DB::KeyCondition const&, DB::UsefulSkipIndexes const&, DB::MergeTreeReaderSettings const&, Poco::Logger*, unsigned long, std::__1::vector<DB::ReadFromMergeTree::IndexStat, std::__1::allocator<DB::ReadFromMergeTree::IndexStat>>&, bool)::$_0::operator()(unsigned long) const
    ./build/./src/Storages/MergeTree/MergeTreeDataSelectExecutor.cpp:1017
void std::__1::__function::__policy_invoker<void ()>::__call_impl<std::__1::__function::__default_alloc_func<DB::MergeTreeDataSelectExecutor::filterPartsByPrimaryKeyAndSkipIndexes(std::__1::vector<std::__1::shared_ptr<DB::IMergeTreeDataPart const>, std::__1::allocator<std::__1::shared_ptr<DB::IMergeTreeDataPart const>>>&&, std::__1::vector<std::__1::shared_ptr<DB::AlterConversions const>, std::__1::allocator<std::__1::shared_ptr<DB::AlterConversions const>>>&&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const>, std::__1::shared_ptr<DB::Context const> const&, DB::KeyCondition const&, DB::UsefulSkipIndexes const&, DB::MergeTreeReaderSettings const&, Poco::Logger*, unsigned long, std::__1::vector<DB::ReadFromMergeTree::IndexStat, std::__1::allocator<DB::ReadFromMergeTree::IndexStat>>&, bool)::$_1, void ()>>(std::__1::__function::__policy_storage const*)
    ./build/./src/Storages/MergeTree/MergeTreeDataSelectExecutor.cpp:1090
ThreadPoolImpl<ThreadFromGlobalPoolImpl<false>>::worker(std::__1::__list_iterator<ThreadFromGlobalPoolImpl<false>, void*>)
    ./build/./base/base/../base/wide_integer_impl.h:809
ThreadFromGlobalPoolImpl<false>::ThreadFromGlobalPoolImpl<void ThreadPoolImpl<ThreadFromGlobalPoolImpl<false>>::scheduleImpl<void>(std::__1::function<void ()>, Priority, std::__1::optional<unsigned long>, bool)::'lambda0'()>(void&&)::'lambda'()::operator()()
    ./build/./src/Common/ThreadPool.cpp:0
ThreadPoolImpl<std::__1::thread>::worker(std::__1::__list_iterator<std::__1::thread, void*>)
    ./build/./base/base/../base/wide_integer_impl.h:809
void* std::__1::__thread_proxy[abi:v15000]<std::__1::tuple<std::__1::unique_ptr<std::__1::__thread_struct, std::__1::default_delete<std::__1::__thread_struct>>, void ThreadPoolImpl<std::__1::thread>::scheduleImpl<void>(std::__1::function<void ()>, Priority, std::__1::optional<unsigned long>, bool)::'lambda0'()>>(void*)
    ./build/./contrib/llvm-project/libcxx/include/__memory/unique_ptr.h:302

    

    

Row 9:
───────
count(): 100
sym:     ZSTD_decompressSequences_bmi2
    ./build/./contrib/zstd/lib/decompress/../common/zstd_internal.h:191
ZSTD_decompressBlock_internal
    ./build/./contrib/zstd/lib/decompress/zstd_decompress_block.c:0
ZSTD_decompressMultiFrame
    ./build/./contrib/zstd/lib/decompress/zstd_decompress.c:0
ZSTD_decompress
    ./build/./contrib/zstd/lib/decompress/zstd_decompress.c:1634
DB::CompressionCodecZSTD::doDecompressData(char const*, unsigned int, char*, unsigned int) const
    ./build/./src/Compression/CompressionCodecZSTD.cpp:93
DB::ICompressionCodec::decompress(char const*, unsigned int, char*) const
    ./build/./src/Compression/ICompressionCodec.cpp:112
DB::CompressedReadBufferFromFile::nextImpl()
    ./build/./src/IO/BufferBase.h:41
DB::ReadBuffer::readStrict(char*, unsigned long)
    /home/milovidov/work/ClickHouse/build/programs/clickhouse
DB::MergeTreeMarksLoader::loadMarksImpl()
    ./build/./src/IO/ReadHelpers.h:116
DB::MergeTreeMarksLoader::loadMarks()
    ./build/./contrib/llvm-project/libcxx/include/__utility/swap.h:37
DB::MergeTreeMarksLoader::getMark(unsigned long, unsigned long)
    ./build/./contrib/llvm-project/libcxx/include/__utility/swap.h:37
DB::MergeTreeReaderStream::init()
    ./build/./src/Storages/MergeTree/MergeTreeReaderStream.cpp:72
DB::MergeTreeReaderStream::adjustRightMark(unsigned long)
    ./build/./src/Storages/MergeTree/MergeTreeReaderStream.cpp:276
DB::MergeTreeIndexReader::MergeTreeIndexReader(std::__1::shared_ptr<DB::IMergeTreeIndex const>, std::__1::shared_ptr<DB::IMergeTreeDataPart const>, unsigned long, DB::MarkRanges const&, DB::MarkCache*, DB::UncompressedCache*, DB::MergeTreeReaderSettings)
    ./build/./contrib/llvm-project/libcxx/include/__memory/unique_ptr.h:274
DB::MergeTreeDataSelectExecutor::filterMarksUsingIndex(std::__1::shared_ptr<DB::IMergeTreeIndex const>, std::__1::shared_ptr<DB::IMergeTreeIndexCondition>, std::__1::shared_ptr<DB::IMergeTreeDataPart const>, DB::MarkRanges const&, DB::Settings const&, DB::MergeTreeReaderSettings const&, DB::MarkCache*, DB::UncompressedCache*, Poco::Logger*)
    ./build/./contrib/llvm-project/libcxx/include/deque:1188
DB::MergeTreeDataSelectExecutor::filterPartsByPrimaryKeyAndSkipIndexes(std::__1::vector<std::__1::shared_ptr<DB::IMergeTreeDataPart const>, std::__1::allocator<std::__1::shared_ptr<DB::IMergeTreeDataPart const>>>&&, std::__1::vector<std::__1::shared_ptr<DB::AlterConversions const>, std::__1::allocator<std::__1::shared_ptr<DB::AlterConversions const>>>&&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const>, std::__1::shared_ptr<DB::Context const> const&, DB::KeyCondition const&, DB::UsefulSkipIndexes const&, DB::MergeTreeReaderSettings const&, Poco::Logger*, unsigned long, std::__1::vector<DB::ReadFromMergeTree::IndexStat, std::__1::allocator<DB::ReadFromMergeTree::IndexStat>>&, bool)::$_0::operator()(unsigned long) const
    ./build/./src/Storages/MergeTree/MergeTreeDataSelectExecutor.cpp:1017
void std::__1::__function::__policy_invoker<void ()>::__call_impl<std::__1::__function::__default_alloc_func<DB::MergeTreeDataSelectExecutor::filterPartsByPrimaryKeyAndSkipIndexes(std::__1::vector<std::__1::shared_ptr<DB::IMergeTreeDataPart const>, std::__1::allocator<std::__1::shared_ptr<DB::IMergeTreeDataPart const>>>&&, std::__1::vector<std::__1::shared_ptr<DB::AlterConversions const>, std::__1::allocator<std::__1::shared_ptr<DB::AlterConversions const>>>&&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const>, std::__1::shared_ptr<DB::Context const> const&, DB::KeyCondition const&, DB::UsefulSkipIndexes const&, DB::MergeTreeReaderSettings const&, Poco::Logger*, unsigned long, std::__1::vector<DB::ReadFromMergeTree::IndexStat, std::__1::allocator<DB::ReadFromMergeTree::IndexStat>>&, bool)::$_1, void ()>>(std::__1::__function::__policy_storage const*)
    ./build/./src/Storages/MergeTree/MergeTreeDataSelectExecutor.cpp:1090
ThreadPoolImpl<ThreadFromGlobalPoolImpl<false>>::worker(std::__1::__list_iterator<ThreadFromGlobalPoolImpl<false>, void*>)
    ./build/./base/base/../base/wide_integer_impl.h:809
ThreadFromGlobalPoolImpl<false>::ThreadFromGlobalPoolImpl<void ThreadPoolImpl<ThreadFromGlobalPoolImpl<false>>::scheduleImpl<void>(std::__1::function<void ()>, Priority, std::__1::optional<unsigned long>, bool)::'lambda0'()>(void&&)::'lambda'()::operator()()
    ./build/./src/Common/ThreadPool.cpp:0
ThreadPoolImpl<std::__1::thread>::worker(std::__1::__list_iterator<std::__1::thread, void*>)
    ./build/./base/base/../base/wide_integer_impl.h:809
void* std::__1::__thread_proxy[abi:v15000]<std::__1::tuple<std::__1::unique_ptr<std::__1::__thread_struct, std::__1::default_delete<std::__1::__thread_struct>>, void ThreadPoolImpl<std::__1::thread>::scheduleImpl<void>(std::__1::function<void ()>, Priority, std::__1::optional<unsigned long>, bool)::'lambda0'()>>(void*)
    ./build/./contrib/llvm-project/libcxx/include/__memory/unique_ptr.h:302

    

    

Row 10:
───────
count(): 96
sym:     DB::Field& DB::Field::operator=<unsigned long>(unsigned long&&)
    ./build/./src/Core/Field.h:919

@alexey-milovidov
Copy link
Member Author

@amosbird maybe you know?

Screenshot_20231016_071809

@alexey-milovidov
Copy link
Member Author

WTF?

Screenshot_20231016_072027

@alexey-milovidov
Copy link
Member Author

https://github.com/ClickHouse/ClickHouse/pull/27961/files#diff-e606245fd4a9bed218932b9548398a29fb2834f7d4bdd683b241b40d01516829

@alexey-milovidov
Copy link
Member Author

#55654

@alexey-milovidov
Copy link
Member Author

@amosbird, it is still slow due to creation and destroying MergeTreeIndexGranuleMinMax objects.

@amosbird
Copy link
Collaborator

maybe you know?

The reason that the default size of index_mark_cache_size is 0 is to keep the original behavior unchanged. Because we didn't have mark_cache for secondary indices before. I saw some improvements when enabling this cache in certain scenarios, however it's not substantial. It's in doubt whether we should enable it by default.

it is still slow due to creation and destroying MergeTreeIndexGranuleMinMax objects.

It's possible to optimize IMergeTreeIndexGranule to reuse current state during reading. Will investigate.

This was referenced Oct 16, 2023
Merged
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
performance
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@amosbird @alexey-milovidov