[SOLVED] Is there any table where I can find all table indexes in clickhouse?

Is there any table where I can find all table indexes in clickhouse?

I can get all table indexes by parsing create_table_query. Is there any table that directly stores index info, like information_schema.STATISTICS in MySQL?

select t.*,splitByString(' TYPE ',trim(index_column)) as index_info,
    mid(index_info[1],POSITION (index_info[1],' ') + 1 ,100) index_columns,
    index_info[2] as index_type
from (
    select database ,name as table_name,engine,create_table_query ,total_rows, partition_key ,sorting_key ,primary_key ,sampling_key ,
        extractAll(create_table_query,'INDEX(.*?)GRANULARITY') index_arr
    from `system`.tables t 
    where database  in ('ods','uds','efc','dss','old')
        and engine not in ('View')
) t 
left ARRAY JOIN index_arr as index_column

Solution

Only tables with ENGINE *MergeTree family have indexes, every *MergeTree table have primary index (not unique) you can know which fields contains in primary index via SELECT * FROM system.columns WHERE is_in_primary_key=1

Also, secondary data skip indexes are available, https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/mergetree/#table_engine-mergetree-data_skipping-indexes

but unfortunately, no system.* table available to show secondary data skip indexes present in table

If you want to known which and how secondary data skip indexes used during query you can use follow SQL queries

SET send_logs_level='trace';
SELECT ... your query;

and look to

Index `index_name` has dropped X/Y granules