Início / dba / Perguntas / 331534
Accepted
Segfault
Asked: 2023-09-25 05:19:55 +0800 CST

Como faço para consultar o maior valor usando um índice

  • 772

Eu tenho uma tabela com vários terabytes de dados de eventos em um esquema muito simples (id, bucket_id, data,created_at) e há um índice como este

create index index_events_on_created_at_and_bucket_id
    on public.events (created_at desc, bucket_id asc);

Agora pensei que seria rápido encontrar o ID do evento mais recente em cada bucket com uma consulta como:

select max(created_at), bucket_id from events group by bucket_id;

Explique a saída:

HashAggregate  (cost=170172168.62..170172178.41 rows=979 width=16)
  Group Key: bucket_id
  ->  Index Only Scan using index_events_on_created_at_and_bucket_id on events  (cost=0.70..156003994.34 rows=2833634856 width=16)

Parece estar usando o índice, mas fazendo uma varredura do índice em vez de apenas capturar o valor principal como eu esperava. De qualquer forma, ele não é concluído em tempo hábil. Suponho que seja um problema ao usar a função agregada na consulta, mas não sei como consertar.

Existe uma consulta que pode retornar o carimbo de data/hora mais recente (ou seja, o primeiro no índice) created_atde cada intervalo, obtendo-o desse índice?

postgresql

1 respostas

  1. Best Answer

    Melhor índice com liderançabucket_id

    Você quer uma linha por balde. Um índice com início bucket_id será muito mais útil.

    CREATE INDEX events_bucket_id_created_at_idx ON events (bucket_id, created_at DESC);

    Relacionado:

    Como você tem um número muito pequeno de valores distintos em bucket_id( "rows=979" ), esta técnica de consulta deve fornecer resultados muito mais rápidos, com base no meu índice sugerido:

    WITH RECURSIVE cte AS (
   (  -- parentheses required
   SELECT bucket_id, created_at
   FROM   events
   ORDER  BY bucket_id, created_at DESC
   LIMIT 1
   )
   
   UNION ALL
   SELECT e.*
   FROM   cte c
   CROSS  JOIN LATERAL (
      SELECT e.bucket_id, e.created_at
      FROM   events e
      WHERE  e.bucket_id > c.bucket_id
      ORDER  BY e.bucket_id, e.created_at DESC
      LIMIT  1
      ) e
   WHERE  c.bucket_id IS NOT NULL
   )
SELECT * FROM cte
WHERE  bucket_id IS NOT NULL;

    Ele emula uma "varredura de índice solto", escolhendo apenas a "primeira" linha para cada distinto bucket_id- exatamente o que você está procurando.

    Observe como a ordem de classificação na consulta corresponde meticulosamente ao índice.

    Se o mapa de visibilidade da tabela estiver atualizado (ou seja, a tabela estiver limpa o suficiente), você obterá varreduras apenas de índice. Deve ser aplicado, já que a consulta lenta que você demonstrou também obteve uma verificação somente de índice. (Embora isso seja uma varredura de todo o índice, em vez de apenas as entradas principais por intervalo). Relacionado:

    Isso pressupõe que ambas as colunas de interesse sejam NOT NULL. Caso contrário, você terá que fazer mais.

    Se você também tiver uma tabela bucketcom uma linha por relevante bucket_id, isso será ainda um pouco mais rápido:

    SELECT b.bucket_id, e.created_at
FROM   bucket b
CROSS  JOIN LATERAL (
   SELECT e.created_at
   FROM   events e
   WHERE  e.bucket_id = b.bucket_id
   ORDER  BY e.created_at DESC
   LIMIT  1
   ) e
ORDER  BY b.bucket_id;

    Ver:

    Preso com índice ativado(created_at DESC, bucket_id ASC)

    Podemos trabalhar com as metainformações adicionais dos seus comentários:

    Eu sei que todos os grupos que me interessam têm eventos recentes

    Você pode aprimorar as consultas acima, mas um ângulo diferente com base nisso deve ter um desempenho melhor:

    SELECT DISTINCT ON (bucket_id)
       bucket_id, created_at
FROM   events
WHERE  created_at > now() - interval '15 minutes'  -- adapt as needed
ORDER  BY bucket_id, created_at DESC;

    Deve ser mais rápido quando limitado à pequena (?) fração das linhas mais recentes. O Postgres pode ler as linhas superiores do índice e alimentá-las DISTINCT ON. Sobre DISTINCT ON:

    • 3

relate perguntas