Início / dba / Perguntas / 287098
Accepted
JPM
Asked: 2021-03-17 01:07:14 +0800 CST

Curinga do TimescaleDB (%) lento

  • 772

Eu tenho uma hipertabela com escala de tempo assim:

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

E indexa da seguinte forma:

create index logs_time_idx
    on logs (time desc);

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

Quando executo esta consulta, leva 20 minutos para ser concluída:

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

Mas quando eu executo este, leva 2 segundos:

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

Tentei indexar apenas as chaves de partição para ajudar a consulta curinga a encontrar valores correspondentes, mas isso não teve efeito.

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

Explique o plano para consulta curinga:

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

Explique para o valor específico da chave de partição:

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)

O TimescaleDB não consegue fazer consultas curinga (%) ou eu perco um índice?

postgresql-12 timescaledb

1 respostas

  1. Best Answer

    Um índice B-Tree não pode ser usado para uma condição LIKE com um curinga ancorado à direita. '%...'ele só pode ser usado para um curinga ancorado à esquerda '...%'. Você precisaria de um índice de trigramas para melhorar isso.

    Se o comprimento de sua pesquisa de substring for sempre o mesmo, você poderá criar um índice nessa expressão. Incluir a coluna de tempo nesse índice provavelmente ajudaria também

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

    E altere sua consulta para:

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

    Como alternativa, crie um índice na string invertida:

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

    Em seguida, altere sua consulta para:

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

relate perguntas