Início / dba / Perguntas / 286565
Accepted
JPM
Asked: 2021-03-07 02:27:04 +0800 CST

Várias agregações na seleção lenta no postgres

  • 772

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

Consulta 

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.

Mas quando combino as consultas 
SELECT max(latitude), max(longitude), min(latitude), min(longitude) 
FROM packets 
WHERE antenna_id IN (1,2)
Duração

[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)

Explique 
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?

postgresql postgresql-12

1 respostas

  1. Best Answer

    Vamos criar alguns dados de teste. Parece que sua consulta tem cerca de 1% de linhas por antena_id, então vamos replicar isso.

    CREATE UNLOGGED TABLE foo( lat FLOAT NOT NULL, lon FLOAT NOT NULL, aid INTEGER NOT NULL );
INSERT INTO foo SELECT random(), random(), random()*100
    FROM generate_series(1,10000000) s;
CREATE INDEX foo_aid_lat ON foo( aid, lat );
CREATE INDEX foo_aid_lon ON foo( aid, lon );
VACUUM ANALYZE foo;

SELECT min(lat),max(lat),min(lon),max(lon) FROM foo WHERE aid IN (1,2);

 Finalize Aggregate  (cost=71572.35..71572.36 rows=1 width=32) (actual time=119.907..125.118 rows=1 loops=1)
   ->  Gather  (cost=71572.12..71572.33 rows=2 width=32) (actual time=119.648..125.110 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=70572.12..70572.13 rows=1 width=32) (actual time=95.595..95.596 rows=1 loops=3)
               ->  Parallel Bitmap Heap Scan on foo  (cost=4886.47..69687.39 rows=88473 width=16) (actual time=9.532..90.336 rows=66524 loops=3)
                     Recheck Cond: (aid = ANY ('{1,2}'::integer[]))
                     Heap Blocks: exact=26477
                     ->  Bitmap Index Scan on foo_aid_lon  (cost=0.00..4833.39 rows=212336 width=0) (actual time=20.022..20.023 rows=199572 loops=1)
                           Index Cond: (aid = ANY ('{1,2}'::integer[]))
 Planning Time: 0.499 ms
 Execution Time: 125.202 ms

    Isso é realmente lento. Vamos tentar um antena_id.

    SELECT min(lat),max(lat),min(lon),max(lon) FROM foo WHERE aid=1;
 Result  (cost=1.88..1.89 rows=1 width=32) (actual time=0.192..0.196 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.43..0.47 rows=1 width=8) (actual time=0.059..0.060 rows=1 loops=1)
           ->  Index Only Scan using foo_aid_lat on foo  (cost=0.43..3777.80 rows=106668 width=8) (actual time=0.057..0.057 rows=1 loops=1)
                 Index Cond: ((aid = 1) AND (lat IS NOT NULL))
                 Heap Fetches: 0
   InitPlan 2 (returns $1)
     ->  Limit  (cost=0.43..0.47 rows=1 width=8) (actual time=0.044..0.045 rows=1 loops=1)
           ->  Index Only Scan Backward using foo_aid_lat on foo foo_1  (cost=0.43..3777.80 rows=106668 width=8) (actual time=0.043..0.044 rows=1 loops=1)
                 Index Cond: ((aid = 1) AND (lat IS NOT NULL))
                 Heap Fetches: 0
   InitPlan 3 (returns $2)
     ->  Limit  (cost=0.43..0.47 rows=1 width=8) (actual time=0.038..0.038 rows=1 loops=1)
           ->  Index Only Scan using foo_aid_lon on foo foo_2  (cost=0.43..3777.80 rows=106668 width=8) (actual time=0.037..0.037 rows=1 loops=1)
                 Index Cond: ((aid = 1) AND (lon IS NOT NULL))
                 Heap Fetches: 0
   InitPlan 4 (returns $3)
     ->  Limit  (cost=0.43..0.47 rows=1 width=8) (actual time=0.042..0.042 rows=1 loops=1)
           ->  Index Only Scan Backward using foo_aid_lon on foo foo_3  (cost=0.43..3777.80 rows=106668 width=8) (actual time=0.041..0.041 rows=1 loops=1)
                 Index Cond: ((aid = 1) AND (lon IS NOT NULL))
                 Heap Fetches: 0
 Planning Time: 0.504 ms
 Execution Time: 0.277 ms

    Esse é o plano correto, que usa o índice de várias colunas para calcular o máximo e o mínimo. Isso requer apenas 1 pesquisa de índice por min() ou max(), porque

    SELECT max(lat) where aid=...

    é equivalente a

    SELECT lat WHERE aid=... ORDER BY lat DESC LIMIT 1

    ...que é otimizável usando um índice que contém as linhas em ordem pré-ordenada.

    A otimização acima de max() e min() é basicamente açúcar sintático, transforma a consulta em um ORDER BY+LIMIT e coloca isso em um InitPlan para usar o índice.

    Mas, aparentemente, não faz isso ao consultar vários antena_ids usando "WHERE IN()". Adicionar um "ajuda GROUP BY" no final da primeira consulta não ajuda.

    Então... Vamos consultar o antena_ids um de cada vez então.

    SELECT * FROM 
(VALUES (1),(2)) AS v
CROSS JOIN LATERAL (SELECT min(lat),max(lat),min(lon),max(lon) FROM foo WHERE aid=v.column1) x;

    Ele faz um loop aninhado sobre os VALUES e dentro do loop aninhado está a consulta rápida acima. Ele retorna o max() e o min() para cada antena_id, então para obter o max() e o min() globais você tem que envolver isso em uma subconsulta e aplicar max() e min() sobre o resultado.

    Isso não deve demorar mais de um milissegundo, a menos que haja outro problema.

    Substituir os VALUES acima por um generate_series(1.100) que obtém o máximo para os 100 auxílios na tabela leva cerca de 5ms. Fazendo à moda antiga:

    select aid,min(lat),max(lat),min(lon),max(lon) FROM foo group by aid;

    demora cerca de 100x mais.

    • 0

relate perguntas