Postgres SQL query lento com mesa grande (AWS RDS)

Question 1

Atualmente, a tabela de ter o mínimo de linha de 30million, e está crescendo, sempre que tentar fazer a consulta SELECT, que levará tempo extremamente longo. O que precisa para otimizar a consulta antes de eu aumentar o desempenho do banco de dados?

POSTGRES 12 on AWS RDS db.t3.small, with 20GB storage

**Message Table**

id (bigint) -> pk
meta (jsonb)
snapshot_ts (integer) -> epoch timestamp
value (character varying 100)
type (character varying 50)
created (timestamp with timezone)
last_modified (timestamp with timezone)
attribute_id (bigint) -> Foreign Key
company_id (bigint) -> Foreign Key
project_id (bigint) -> Foreign Key
device_id (bigint) -> Foreign Key


EXPLAIN (analyze,buffers) SELECT COUNT(*) FROM public.message
WHERE company_id=446 AND project_id=52 AND snapshot_ts>=1637568000.0 AND snapshot_ts<=1637654399.0 AND attribute_id=458

->Aggregate  (cost=399804.26..399804.27 rows=1 width=8) (actual time=65150.696..65150.697 rows=1 loops=1)
  Buffers: shared hit=170 read=115437 dirtied=167
  I/O Timings: read=64396.424
  ->  Index Scan using message_attribute_id_6578b282 on message  (cost=0.56..399803.23 rows=411 width=0) (actual time=57752.297..65147.391 rows=8656 loops=1)
        Index Cond: (attribute_id = 458)
        Filter: ((company_id = 446) AND (project_id = 52) AND ((snapshot_ts)::numeric >= 1637568000.0) AND ((snapshot_ts)::numeric <= 1637654399.0))
        Rows Removed by Filter: 106703
        Buffers: shared hit=170 read=115437 dirtied=167
        I/O Timings: read=64396.424
Planning Time: 0.779 ms
Execution Time: 65150.730 ms

**Indexes**
indexname                       | indexdef
message_attribute_id_6578b282   | CREATE INDEX message_attribute_id_6578b282 ON public.message USING btree (attribute_id)
message_company_id_cef5ed5f     | CREATE INDEX message_company_id_cef5ed5f ON public.message USING btree (company_id)
message_device_id_b4da2571      | CREATE INDEX message_device_id_b4da2571 ON public.message USING btree (device_id)
message_pkey                    | CREATE UNIQUE INDEX message_pkey ON public.message USING btree (id)
message_project_id_7ba6787d     | CREATE INDEX message_project_id_7ba6787d ON public.message USING btree (project_id)

Question 2

Considerando a consulta específica:

SELECT COUNT(*)
FROM public.message
WHERE company_id=446 
  AND project_id=52 
  AND snapshot_ts>=1637568000.0 AND snapshot_ts<=1637654399.0 
  AND attribute_id=458

o índice a seguir tem o potencial de aumentar grandemente o desempenho:

create index ix1 on public.message (
  company_id, project_id, attribute_id, snapshot_ts
);

No entanto, tenha em mente que a criação de um índice de 30 milhões de linhas da tabela pode demorar algum tempo.

The Impaler · Answer 1 · 2021-11-24T03:41:16

2

Considerando a consulta específica:

SELECT COUNT(*)
FROM public.message
WHERE company_id=446 
  AND project_id=52 
  AND snapshot_ts>=1637568000.0 AND snapshot_ts<=1637654399.0 
  AND attribute_id=458

o índice a seguir tem o potencial de aumentar grandemente o desempenho:

create index ix1 on public.message (
  company_id, project_id, attribute_id, snapshot_ts
);

No entanto, tenha em mente que a criação de um índice de 30 milhões de linhas da tabela pode demorar algum tempo.

The Impaler

2021-11-24 03:41:16

é que isso quer dizer vamos dizer que eu tenho de casos múltiplos condição de consulta, que eu preciso para criar cada índice para cada caso? "(company_id, project_id, attribute_id, snapshot_ts)", "(project_id, attribute_id, snapshot_ts)", "(attribute_id, snapshot_ts)"

Sola

@sola se você precisar a exata ótimo índice para cada consulta, então sim, pode ser um monte de índices. Mas você provavelmente pode começar afastado com um pouco menos ideal para alguns deles. Tentar e ver. Se você tiver perguntas, certifique-se de incluir a EXPLICAR (ANALISAR, BUFFERS)

jjanes

depois de criar o índice na minha máquina local, e o trabalho no início, mas depois de algum tempo, ele não disparar os índices ao fazer a consulta. Isso aconteceu no servidor de produção bem.

Sola

@Sola Se a consulta não estiver usando o índice e, em seguida, o otimizador de ele considerar um plano de execução diferentes. Primeiro, certifique-se de que as estatísticas da tabela são atualizados usando ANALYZE public.message. Em seguida, se o problema ainda persistir, por favor, recuperar o plano de execução e adicioná-lo para a questão.

The Impaler

obrigado pela resposta. Ontem, ao tentar com o índice (company_id, project_id, attribute_id, snapshot_ts), em condições de uso (snapshot_ts e attribute_id), trabalhando em primeiro lugar, então não. Agora eu adicionar outro índice com (attribute_id, snapshot_ts), para este efeito, parecem trabalhar novamente, irá continuar a monitorizar.

Sola

Postgres SQL query lento com mesa grande (AWS RDS)

Pergunta

Melhor resposta

Em outros idiomas

Esta página está em outros idiomas

Popular nesta categoria

Perguntas populares nesta categoria