TimescaleDB通配符(%)慢

Question

我有一个这样的时刻表：

create table logs
(
    time         timestamp not null,
    partitionkey text      not null,
    ip           inet,
    raw          text,
    transformed  double precision
);

和指数如下：

create index logs_time_idx
    on logs (time desc);

create unique index logs_partitionkey_time_uindex
    on logs (partitionkey asc, time desc);

当我运行这个查询时，需要20分钟才能完成：

SELECT * FROM data.logs 
WHERE partitionkey LIKE '%m.60.05482730' 
AND time > NOW() - INTERVAL '3 days'

但是当我运行这个时，需要2秒：

SELECT * FROM data.logs 
WHERE partitionkey LIKE '865617033605366.m.60.05482730'
AND time > NOW() - INTERVAL '3 days'

我尝试只为分区键建立索引，以帮助通配符查询找到匹配的值，但没有效果。

-- created this index later to try and fix the slow wildcard query
create index logs_partitionkey_index
    on logs (partitionkey);

解释通配符查询计划：

Gather  (cost=1000.57..525711.89 rows=1219 width=81)
  Workers Planned: 2
  ->  Parallel Custom Scan (ChunkAppend) on logs  (cost=0.57..524589.99 rows=509 width=82)
        Chunks excluded during startup: 2
        ->  Parallel Index Scan using _hyper_2_10_chunk_logs_time_idx on _hyper_2_10_chunk  (cost=0.57..263956.91 rows=255 width=81)
              Index Cond: ("time" > (now() - '3 days'::interval))
              Filter: (partitionkey ~~ '%m.60.05482730'::text)
        ->  Parallel Index Scan using _hyper_2_9_chunk_logs_time_idx on _hyper_2_9_chunk  (cost=0.57..260629.72 rows=252 width=83)
              Index Cond: ("time" > (now() - '3 days'::interval))
              Filter: (partitionkey ~~ '%m.60.05482730'::text)
JIT:
  Functions: 8
  Options: Inlining true, Optimization true, Expressions true, Deforming true

解释特定的partionkey值：

Custom Scan (ChunkAppend) on logs  (cost=0.44..903.08 rows=790 width=82)
  Chunks excluded during startup: 2
  ->  Index Scan using _hyper_2_9_chunk_logs_partitionkey_time_uindex on _hyper_2_9_chunk  (cost=0.57..447.44 rows=392 width=83)
        Index Cond: ((partitionkey = '865617033605366.m.60.05482730'::text) AND ("time" > (now() - '3 days'::interval)))
        Filter: (partitionkey ~~ '865617033605366.m.60.05482730'::text)
  ->  Index Scan using _hyper_2_10_chunk_logs_partitionkey_time_uindex on _hyper_2_10_chunk  (cost=0.57..452.27 rows=396 width=81)
        Index Cond: ((partitionkey = '865617033605366.m.60.05482730'::text) AND ("time" > (now() - '3 days'::interval)))
        Filter: (partitionkey ~~ '865617033605366.m.60.05482730'::text)

TimescaleDB无法执行通配符(%)查询，还是缺少索引？

Answer 1

B树索引不能用于具有右锚定通配符的类似条件.'%...'它只能用于左锚式通配符'...%'。您需要一个trigram索引来改进它。

如果子字符串搜索的长度总是相同的，则可以在该表达式上创建索引。在索引中包括时间列可能也有帮助。

create index logs_partitionkey_index
    on logs ( (right(partitionkey,13), "time" );

并将查询更改为：

SELECT * 
FROM data.logs 
WHERE right(partitionkey,13) = 'm.60.05482730' 
  AND "time" > NOW() - INTERVAL '3 days'

或者，在反向字符串上创建一个索引：

create index logs_partitionkey_index
    on logs ( (reverse(partitionkey) varchar_pattern_ops);

然后将查询更改为：

SELECT * 
FROM data.logs 
WHERE reverse(partitionkey) like reverse('m.60.05482730')||'%'
  AND "time" > NOW() - INTERVAL '3 days'