主页 / dba / 问题 / 287098
Accepted
JPM
Asked: 2021-03-17 01:07:14 +0800 CST

TimescaleDB 通配符 (%) 慢

  • 772

我有一个像这样的 timescaledb 超表：

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 是否无法执行通配符 (%) 查询，还是我错过了索引？

postgresql-12 timescaledb

1 个回答

  1. Best Answer

    B-Tree 索引不能用于带有右锚定通配符的 LIKE 条件。'%...'它只能用于左锚通配符'...%'。你需要一个三元组索引来改进它。

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

    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'
    • 0

相关问题