Início / dba / Perguntas / 177162
Accepted
Kokizzu
Asked: 2017-06-25 00:29:51 +0800 CST

Como tornar o DISTINCT ON mais rápido no PostgreSQL?

  • 772

Eu tenho uma tabela station_logsem um banco de dados PostgreSQL 9.6:

    Column     |            Type             |    
---------------+-----------------------------+
 id            | bigint                      | bigserial
 station_id    | integer                     | not null
 submitted_at  | timestamp without time zone | 
 level_sensor  | double precision            | 
Indexes:
    "station_logs_pkey" PRIMARY KEY, btree (id)
    "uniq_sid_sat" UNIQUE CONSTRAINT, btree (station_id, submitted_at)

Estou tentando obter o último level_sensorvalor com base em submitted_at, para cada station_id. Existem cerca de 400 station_idvalores exclusivos e cerca de 20 mil linhas por dia por station_id.

Antes de criar o índice:

EXPLAIN ANALYZE
SELECT DISTINCT ON(station_id) station_id, submitted_at, level_sensor
FROM station_logs ORDER BY station_id, submitted_at DESC;

Exclusivo (custo=4347852.14..4450301.72 linhas=89 largura=20) (tempo real=22202.080..27619.167 linhas=98 loops=1)
   -> Classificar (custo=4347852.14..4399076.93 linhas=20489916 largura=20) (tempo real=22202.077..26540.827 linhas=20489812 loops=1)
         Chave de classificação: station_id, submit_at DESC
         Método de classificação: mesclagem externa Disco: 681040kB
         -> Seq Scan em station_logs (custo=0.00..598895.16 linhas=20489916 largura=20) (tempo real=0.023..3443.587 linhas=20489812 loops=$
 Tempo de planejamento: 0,072 ms
 Tempo de execução: 27690,644 ms

Criando índice:

CREATE INDEX station_id__submitted_at ON station_logs(station_id, submitted_at DESC);

Após criar o índice, para a mesma consulta:

Exclusivo (custo=0,56..2156367,51 linhas=89 largura=20) (tempo real=0,184..16263.413 linhas=98 loops=1)
   -> Varredura de índice usando station_id__submitted_at em station_logs (custo=0.56..2105142.98 linhas=20489812 largura=20) (tempo real=0.181..1$
 Tempo de planejamento: 0,206 ms
 Tempo de execução: 16263,490 ms

Existe uma maneira de tornar essa consulta mais rápida? Como 1 segundo, por exemplo, 16 segundos ainda é muito.

postgresql performance

2 respostas

  1. Best Answer

    Para apenas 400 estações, essa consulta será muito mais rápida:

    SELECT s.station_id, l.submitted_at, l.level_sensor
FROM   station s
CROSS  JOIN LATERAL (
   SELECT submitted_at, level_sensor
   FROM   station_logs
   WHERE  station_id = s.station_id
   ORDER  BY submitted_at DESC NULLS LAST
   LIMIT  1
   ) l;

    dbfiddle aqui (comparando planos para esta consulta, alternativa de Abelisto e seu original)

    Resultando EXPLAIN ANALYZEconforme fornecido pelo OP:

    Nested Loop  (cost=0.56..356.65 rows=102 width=20) (actual time=0.034..0.979 rows=98 loops=1)
   ->  Seq Scan on stations s  (cost=0.00..3.02 rows=102 width=4) (actual time=0.009..0.016 rows=102 loops=1)
   ->  Limit  (cost=0.56..3.45 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=102)
         ->  Index Scan using station_id__submitted_at on station_logs  (cost=0.56..664062.38 rows=230223 width=16) (actual time=0.009$
               Index Cond: (station_id = s.id)
 Planning time: 0.542 ms
 Execution time: <b>1.013 ms</b>  -- !!

    O único índice que você precisa é aquele que você criou: station_id__submitted_at. A UNIQUErestrição uniq_sid_sattambém faz o trabalho, basicamente. Manter ambos parece um desperdício de espaço em disco e desempenho de gravação.

    Eu adicionei NULLS LASTna ORDER BYconsulta porque submitted_atnão está definido NOT NULL. Idealmente, se aplicável!, adicione uma NOT NULLrestrição à coluna submitted_at, elimine o índice adicional e remova NULLS LASTda consulta.

    Se submitted_atpuder ser NULL, crie este UNIQUEíndice para substituir o índice atual e a restrição exclusiva:

    CREATE UNIQUE INDEX station_logs_uni ON station_logs(station_id, submitted_at DESC NULLS LAST);

    Considerar:

    Isso pressupõe uma tabela separadastation com uma linha por relevante station_id(normalmente o PK) - que você deve ter de qualquer maneira. Se você não tem, crie. Novamente, muito rápido com esta técnica rCTE:

    CREATE TABLE station AS
WITH RECURSIVE cte AS (
   (
   SELECT station_id
   FROM   station_logs
   ORDER  BY station_id
   LIMIT  1
   )
   UNION ALL
   SELECT l.station_id
   FROM   cte c
   ,      LATERAL (   
      SELECT station_id
      FROM   station_logs
      WHERE  station_id > c.station_id
      ORDER  BY station_id
      LIMIT  1
      ) l
   )
TABLE cte;

    Eu uso isso no violino também. Você pode usar uma consulta semelhante para resolver sua tarefa diretamente, sem stationtabela - se não puder ser convencido a criá-la.

    Instruções detalhadas, explicações e alternativas:

    Índice de otimização

    Sua consulta deve ser muito rápida agora. Somente se você ainda precisar otimizar o desempenho de leitura ...

    Pode fazer sentido adicionar level_sensorcomo última coluna ao índice para permitir varreduras somente de índice , como joanolo commented .
    Contra: Torna o índice maior - o que adiciona um pequeno custo a todas as consultas que o utilizam.
    Pro: Se você realmente obtiver apenas varreduras de índice, a consulta em questão não precisará visitar as páginas de heap, o que a torna duas vezes mais rápida. Mas isso pode ser um ganho insubstancial para a consulta muito rápida agora.

    No entanto , não espero que funcione para o seu caso. Você mencionou:

    ... cerca de 20 mil linhas por dia por station_id.

    Normalmente, isso indicaria uma carga de gravação incessante (1 a station_idcada 5 segundos). E você está interessado na última linha. As varreduras somente de índice funcionam apenas para páginas de heap visíveis para todas as transações (o bit no mapa de visibilidade está definido). Você teria que executar VACUUMconfigurações extremamente agressivas para a tabela acompanhar a carga de gravação e ainda não funcionaria na maioria das vezes. Se minhas suposições estiverem corretas, as varreduras somente de índice estão fora, não adicione level_sensorao índice.

    OTOH, se minhas suposições se confirmarem e sua tabela estiver crescendo muito , um índice BRIN pode ajudar. Relacionado:

    Ou ainda mais especializado e mais eficiente: um índice parcial apenas para as últimas adições para cortar a maior parte das linhas irrelevantes:

    CREATE INDEX station_id__submitted_at_recent_idx ON station_logs(station_id, submitted_at DESC NULLS LAST)
WHERE submitted_at > '2017-06-24 00:00';

    Escolha um carimbo de data/hora para o qual você sabe que as linhas mais recentes devem existir. Você precisa adicionar uma WHEREcondição de correspondência a todas as consultas, como:

    ...
WHERE  station_id = s.station_id
AND    submitted_at > '2017-06-24 00:00'
...

    Você precisa adaptar o índice e a consulta de tempos em tempos.
    Respostas relacionadas com mais detalhes:

    • 26

  2. Experimente a forma clássica:

    create index idx_station_logs__station_id on station_logs(station_id);
create index idx_station_logs__submitted_at on station_logs(submitted_at);

analyse station_logs;

with t as (
  select station_id, max(submitted_at) submitted_at 
  from station_logs 
  group by station_id)
select * 
from t join station_logs l on (
  l.station_id = t.station_id and l.submitted_at = t.submitted_at);

    dbfiddle

    EXPLIQUE ANALISAR por ThreadStarter

     Nested Loop  (cost=701344.63..702110.58 rows=4 width=155) (actual time=6253.062..6253.544 rows=98 loops=1)
   CTE t
     ->  HashAggregate  (cost=701343.18..701344.07 rows=89 width=12) (actual time=6253.042..6253.069 rows=98 loops=1)
           Group Key: station_logs.station_id
           ->  Seq Scan on station_logs  (cost=0.00..598894.12 rows=20489812 width=12) (actual time=0.034..1841.848 rows=20489812 loop$
   ->  CTE Scan on t  (cost=0.00..1.78 rows=89 width=12) (actual time=6253.047..6253.085 rows=98 loops=1)
   ->  Index Scan using station_id__submitted_at on station_logs l  (cost=0.56..8.58 rows=1 width=143) (actual time=0.004..0.004 rows=$
         Index Cond: ((station_id = t.station_id) AND (submitted_at = t.submitted_at))
 Planning time: 0.542 ms
 Execution time: 6253.701 ms
    • 6

relate perguntas