JPM's questions

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?

Eu tenho uma tabela com colunas: id, antena_id, latitude, longitude. Existem dois índices compostos em (antenna_id, latitude) e (antenna_id, longitude). Quando eu faço um max(latitude) para um id de antena específico, a velocidade é aceitável, mas fazer um min e max para latitude e longitude ao mesmo tempo é muito lento.

Usando PostgreSQL 12.3

EXPLAIN (analyze, buffers, format text) 
SELECT max(latitude) 
FROM packets 
WHERE antenna_id IN (1,2)

Finalize Aggregate  (cost=443017.21..443017.22 rows=1 width=32) (actual time=4373.679..4373.679 rows=1 loops=1)
  Buffers: shared hit=10812 read=16887
  ->  Gather  (cost=443017.10..443017.21 rows=1 width=32) (actual time=4373.412..4389.032 rows=2 loops=1)
        Workers Planned: 1
        Workers Launched: 1
        Buffers: shared hit=10812 read=16887
        ->  Partial Aggregate  (cost=442017.10..442017.11 rows=1 width=32) (actual time=4313.576..4313.577 rows=1 loops=2)
              Buffers: shared hit=10809 read=16887
              ->  Parallel Index Only Scan using idx_packets_antenna_id_latitude on packets  (cost=0.57..433527.51 rows=3395835 width=7) (actual time=0.375..3435.488 rows=2201866 loops=2)
                    Index Cond: (antenna_id = ANY ('{1,2}'::integer[]))
                    Heap Fetches: 0
                    Buffers: shared hit=10809 read=16887
Planning Time: 5.992 ms
JIT:
  Functions: 8
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 6.236 ms, Inlining 0.000 ms, Optimization 1.549 ms, Emission 32.058 ms, Total 39.842 ms
Execution Time: 4706.406 ms

A explicação parece quase idêntica para max(longitude), min(latitude) e min(longitude) por conta própria. A velocidade é aceitável.

SELECT max(latitude), max(longitude), min(latitude), min(longitude) 
FROM packets 
WHERE antenna_id IN (1,2)

[2021-03-06 09:28:30] 1 row retrieved starting from 1 in 5 m 35 s 907 ms (execution: 5 m 35 s 869 ms, fetching: 38 ms)

Finalize Aggregate  (cost=3677020.18..3677020.19 rows=1 width=128)
  ->  Gather  (cost=3677020.06..3677020.17 rows=1 width=128)
        Workers Planned: 1
        ->  Partial Aggregate  (cost=3676020.06..3676020.07 rows=1 width=128)
              ->  Parallel Seq Scan on packets  (cost=0.00..3642080.76 rows=3393930 width=14)
                    Filter: (antenna_id = ANY ('{1,2}'::integer[]))
JIT:
  Functions: 7
  Options: Inlining true, Optimization true, Expressions true, Deforming true

EXPLAIN (analyze, buffers, format text) 
SELECT max(latitude), max(longitude), min(latitude), min(longitude) 
FROM packets 
WHERE antenna_id IN (1,2)

está em execução há 24 horas e ainda não está pronto

Índices

create index idx_packets_antenna_id_time
    on packets (antenna_id, time);

create index idx_packets_antenna_id_longitude
    on packets (antenna_id, longitude);

create index idx_packets_device_id_time
    on packets (device_id, time);

create index idx_packets_antenna_id_latitude
    on packets (antenna_id, latitude);

Estatísticas de dados

select count(*) from packets
136758098

select count(distinct (antenna_id)) from packets
17558

select antenna_id, count(*) as records 
from packets 
where antenna_id in (1,2) 
group by antenna_id 
order by records desc

1,4361049
2,42683

Pergunta

Por que a segunda consulta que faz o min e max no campo de latitude e longitude não usa os índices? E como posso reescrever a consulta para que seja mais rápida?

Eu tenho um programa golang que faz computação em dados em vários threads ao mesmo tempo, todos puxando seus dados do Postgres. O número de threads depende de um resultado anterior. Portanto, pode haver centenas de threads tentando extrair dados do Postgres ao mesmo tempo.

A biblioteca sql golang permite especificar um limite de conexão, o que evita que o postgres fique sem memória compartilhada ou conexões livres.

Se eu codificar o número máximo de conexões, ficarei sem conexões quando outra coisa estiver conectada. Por outro lado, se eu codificar um número muito baixo de conexões permitidas do programa golang, o desempenho será limitado desnecessariamente.

Qual seria a melhor maneira de permitir que o programa go use o máximo de conexões possíveis, sem ultrapassar os limites. Imagino que esse número seja variável dependendo do número de outros serviços que estão conectados ao banco de dados naquele momento.

Estou pensando em executar o PgBouncer entre o banco de dados e o programa golang, esperançosamente aceitando todas as conexões do programa golang, permitindo o maior número possível, mas bloqueando o resto até que as conexões fiquem livres. No entanto, não tenho certeza se o PgBouncer faz isso, mas testarei isso a seguir.

Existe talvez outro método de ter um pool de conexões que bloqueará conexões quando nenhuma conexão real estiver livre? Bloquear, não recusar, pois recusar uma conexão significa que terei que adicionar lógica de repetição ao meu programa golang.

Este é um post cruzado de um problema que eu coloco no repositório GORM Github. Não tenho certeza se o problema está no Postgres 11 ou no GORM.

Veja: https://github.com/jinzhu/gorm/issues/2872

Tenho uma tabela contendo um ID como chave primária e apenas mais uma coluna (mega_herz) que é uma numérica(7,3). O campo numérico também possui uma restrição exclusiva. Quando faço a seguinte consulta de pgadmin4 ou psql recebo uma linha como resposta:

SELECT * 
FROM "ttnmapper_frequencies" 
WHERE ("ttnmapper_frequencies"."mega_herz" = 868.3) 
ORDER BY "ttnmapper_frequencies"."id" ASC 
LIMIT 1

Mas quando faço a mesma consulta via GORM ele não retorna nenhum resultado, e quando tento inserir ele falha:

(/home/jpmeijers/go/src/ttnmapper-postgres-insert-raw/main.go:342) 
[2020-02-03 14:52:28]  [3.07ms]  
SELECT * 
FROM "ttnmapper_frequencies"  
WHERE ("ttnmapper_frequencies"."mega_herz" = 868.3)
ORDER BY "ttnmapper_frequencies"."id" ASC 
LIMIT 1  
[0 rows affected or returned ] 

(/home/jpmeijers/go/src/ttnmapper-postgres-insert-raw/main.go:342) 
[2020-02-03 14:52:28]  [2.10ms]  
INSERT  INTO "ttnmapper_frequencies" 
("mega_herz") 
VALUES 
(868.3) 
RETURNING "ttnmapper_frequencies"."id"  
[0 rows affected or returned ] 

(/home/jpmeijers/go/src/ttnmapper-postgres-insert-raw/main.go:345) 
[2020-02-03 14:52:28]  pq: duplicate key value violates unique constraint "ttnmapper_frequencies_mega_herz_key" 

Por que a consulta select não retornaria nenhum resultado? Devo especificar o número com três casas decimais na cláusula where?

Atualização 2020-02-09:

A saída de log do Postgres mostra o seguinte:

2020-02-09 05:43:45.679 UTC [59458] ttnmapper@ttnmapper LOG:  execute <unnamed>: SELECT * FROM "ttnmapper_frequencies"  WHERE ("ttnmapper_frequencies"."mega_herz" = $1) ORDER BY "ttnmapper_frequencies"."id" ASC LIMIT 1
2020-02-09 05:43:45.679 UTC [59458] ttnmapper@ttnmapper DETAIL:  parameters: $1 = '868.2999877929688'
2020-02-09 05:43:45.688 UTC [59458] ttnmapper@ttnmapper LOG:  statement: BEGIN READ WRITE
2020-02-09 05:43:45.689 UTC [59458] ttnmapper@ttnmapper LOG:  execute <unnamed>: INSERT  INTO "ttnmapper_frequencies" ("mega_herz") VALUES ($1) RETURNING "ttnmapper_frequencies"."id"
2020-02-09 05:43:45.689 UTC [59458] ttnmapper@ttnmapper DETAIL:  parameters: $1 = '868.2999877929688'
2020-02-09 05:43:45.704 UTC [59458] ttnmapper@ttnmapper ERROR:  duplicate key value violates unique constraint "ttnmapper_frequencies_mega_herz_key"
2020-02-09 05:43:45.704 UTC [59458] ttnmapper@ttnmapper DETAIL:  Key (mega_herz)=(868.300) already exists.

A pergunta

SELECT * FROM "ttnmapper_frequencies" WHERE ("ttnmapper_frequencies"."mega_herz" = 868.2999877929688) ORDER BY "ttnmapper_frequencies"."id" ASC LIMIT 1

não retorna nenhum resultado.

Mas, no entanto, a consulta

SELECT * FROM "ttnmapper_frequencies" WHERE ("ttnmapper_frequencies"."mega_herz" = 868.3) ORDER BY "ttnmapper_frequencies"."id" ASC LIMIT 1

retorna uma única linha.

Portanto, tenho que assumir que o Postgres não arredonda automaticamente os parâmetros para consultas selecionadas. Qual seria a melhor solução para isso?