Postgres SQL de la consulta lento con mesa grande (AWS RDS)

Question 1

Actualmente, la tabla tiene un mínimo de fila de 30million, y está creciendo, cada vez que tratamos de hacer consulta de selección, se llevará a muy largo plazo. Lo que necesita para optimizar la consulta antes de aumentar el rendimiento de la base de datos?

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

Teniendo en cuenta la 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

el siguiente índice tiene el potencial de aumentar en gran medida el rendimiento:

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

Sin embargo, tenga en cuenta que la creación de un índice en una de 30 millones de fila de una tabla puede tomar algún tiempo.

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

2

Teniendo en cuenta la 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

el siguiente índice tiene el potencial de aumentar en gran medida el rendimiento:

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

Sin embargo, tenga en cuenta que la creación de un índice en una de 30 millones de fila de una tabla puede tomar algún tiempo.

The Impaler

2021-11-24 03:41:16

es que decir que vamos a decir que tengo varios de caso de la condición de la consulta, necesito crear cada uno de los índices para cada caso? "(company_id, project_id, attribute_id, snapshot_ts)", "(project_id, attribute_id, snapshot_ts)", "(attribute_id, snapshot_ts)"

Sola

@sola si usted necesita la exacta índice óptimo para cada consulta, entonces sí que puede ser un montón de índices. Pero probablemente usted puede conseguir lejos con un poco menos óptimo para algunos de ellos. Pruebe con un par de y ver. Si usted tiene preguntas, asegúrese de incluir la EXPLIQUE (ANALIZAR, BÚFERES)

jjanes

después de crear un índice en mi máquina local, y el trabajo al principio, pero después de algún tiempo, que no activaba los índices cuando se hace la consulta. Esto ocurrió en el servidor de producción así.

Sola

@Sola Si la consulta no es con el índice, a continuación, el optimizador que teniendo en cuenta un plan de ejecución diferente. En primer lugar, asegúrese de que las estadísticas de la tabla son hasta la fecha el uso de ANALYZE public.message. Entonces, si el problema persiste, por favor, recuperar el plan de ejecución y agregar a la pregunta.

The Impaler

gracias por la respuesta. Ayer cuando tratando con índice (company_id, project_id, attribute_id, snapshot_ts), en condición de uso (snapshot_ts y attribute_id), funcionando a la primera, entonces no. Ahora puedo añadir otro índice con (attribute_id, snapshot_ts), para este propósito, se parecen a trabajar de nuevo, continuará monitor.

Sola

Postgres SQL de la consulta lento con mesa grande (AWS RDS)

Pregunta

Mejor respuesta

En otros idiomas

Esta página está en otros idiomas

Popular en esta categoría

Las preguntas más habituales en esta categoría