Repository: ClickHouse/ClickHouse
File: tests/queries/0_stateless/03620_distributed_index_analysis_vector_search.sql (SQL)

Code before:
LIMIT 4
SETTINGS use_skip_indexes=0;

SELECT 'ANN results';
SELECT *
FROM dist_vec
ORDER BY L2Distance(vec, [0.3, 0.3]) ASC
LIMIT 4;

SELECT 'ANN results - distributed';
SELECT *
FROM dist_vec
ORDER BY L2Distance(vec, [0.3, 0.3]) ASC
LIMIT 4
SETTINGS distributed_index_analysis_for_non_shared_merge_tree = 1, distributed_index_analysis = 1, max_parallel_replicas = 3, cluster_for_parallel_replicas = 'parallel_replicas';

-- Common from  03620_distributed_index_analysis.sql
system flush logs query_log;
select format(
  'distributed_index_analysis={}, DistributedIndexAnalysisMicroseconds>0={}, DistributedIndexAnalysisMissingParts={}, DistributedIndexAnalysisScheduledReplicas={}, DistributedIndexAnalysisFailedReplicas>0={}',
  Settings['distributed_index_analysis'],
  ProfileEvents['DistributedIndexAnalysisMicroseconds'] > 0,
  ProfileEvents['DistributedIndexAnalysisMissingParts'],
  ProfileEvents['DistributedIndexAnalysisScheduledReplicas'],
  ProfileEvents['DistributedIndexAnalysisFailedReplicas'] > 0
)
from system.query_log
where
  current_database = currentDatabase()
  and event_date >= yesterday()
  and type = 'QueryFinish'
  and query_kind = 'Select'
  and is_initial_query
  and has(Settings, 'distributed_index_analysis')
  and endsWith(log_comment, '-' || currentDatabase())
order by event_time_microseconds;


Review comment (performance):
Let's also add a check for number of `VectorSimilarityIndexCacheHits` (+`VectorSimilarityIndexCacheMisses`) for the `mergeTreeAnalyzeIndexes` subqueries, to make sure that ANN index was applied there?

Code after:
LIMIT 4
SETTINGS use_skip_indexes=0;

SELECT 'ANN results';
SELECT *
FROM dist_vec
ORDER BY L2Distance(vec, [0.3, 0.3]) ASC
LIMIT 4;

SELECT 'ANN results - distributed';
SELECT *
FROM dist_vec
ORDER BY L2Distance(vec, [0.3, 0.3]) ASC
LIMIT 4
SETTINGS distributed_index_analysis_for_non_shared_merge_tree = 1, distributed_index_analysis = 1, max_parallel_replicas = 3, cluster_for_parallel_replicas = 'parallel_replicas';

-- Common from 03620_distributed_index_analysis.sql
system flush logs query_log;
select format(
  'distributed_index_analysis={}, DistributedIndexAnalysisMicroseconds>0={}, DistributedIndexAnalysisMissingParts={}, DistributedIndexAnalysisScheduledReplicas={}, DistributedIndexAnalysisFailedReplicas>0={}',
  Settings['distributed_index_analysis'],
  ProfileEvents['DistributedIndexAnalysisMicroseconds'] > 0,
  ProfileEvents['DistributedIndexAnalysisMissingParts'],
  ProfileEvents['DistributedIndexAnalysisScheduledReplicas'],
  ProfileEvents['DistributedIndexAnalysisFailedReplicas'] > 0
)
from system.query_log
where
  current_database = currentDatabase()
  and event_date >= yesterday()
  and type = 'QueryFinish'
  and query_kind = 'Select'
  and is_initial_query
  and has(Settings, 'distributed_index_analysis')
  and endsWith(log_comment, '-' || currentDatabase())
order by event_time_microseconds;

-- Presence of this metric confirms vector index was used after index analysis
select sum(ProfileEvents['USearchSearchCount']) > 0
from system.query_log
where initial_query_id = (select query_id
        from system.query_log
        where
        current_database = currentDatabase()
        and event_date >= yesterday()
        and type = 'QueryFinish'
        and query_kind = 'Select'
        and is_initial_query
        and has(Settings, 'distributed_index_analysis')
        and endsWith(log_comment, '-' || currentDatabase()));