Slow query, because uses match()

[jkbnerad]

At the begging of backup the program call

SELECT
    database,
    name,
    engine,
    data_paths,
    uuid,
    create_table_query,
    coalesce(total_bytes, 0) AS total_bytes
FROM system.tables
WHERE (is_temporary = 0) AND match(concat(database, '.', name), '^db_name\\.table_name$')
ORDER BY total_bytes DESC
SETTINGS show_table_uuid_in_table_create_query_if_not_nil = 1

It is very inefficient. It take 10+ minutes in my case.

Why not use:

SELECT
    database,
    name,
    engine,
    data_paths,
    uuid,
    create_table_query,
    coalesce(total_bytes, 0) AS total_bytes
FROM system.tables
WHERE (is_temporary = 0) AND (database = 'db_name') AND (table = 'table_name')
ORDER BY total_bytes DESC
FORMAT Vertical
SETTINGS show_table_uuid_in_table_create_query_if_not_nil = 1

It takes few seconds.

[Slach]

match() applied here cause you can use --tables=db.* for example
usually even when you have 10k tables this query should be fast

could you share?

SELECT count() FROM system.tables WHERE is_temporary=0
SELECT DISTINCT engine, count() FROM system.tables GROUP BY engine

[jkbnerad]

Ah,
it turns out that my query_log configuration was incorrect, resulting in over 1 million+ empty query_log tables. After correcting this and reducing the number to just 250 tables, the performance has improved significantly, and it's now extremely fast.

Many thanks for your assistance.