Postgres SQL de la requête lente avec une grande table (AWS RDS)

Question 1

Actuellement, la table de la durée minimale de la ligne de 30million, et il est croissante, à chaque fois essayer de faire une requête SELECT, il prendre la très longue période de temps. Quel est le besoin pour optimiser la requête avant de m'améliorer les performances de la base de données?

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

Considérant la requête spécifique:

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

l'index suivant a le potentiel d'accroître considérablement la performance:

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

Cependant, gardez à l'esprit que la création d'un index de 30 millions de lignes de la table peut prendre un certain temps.

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

2

Considérant la requête spécifique:

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

l'index suivant a le potentiel d'accroître considérablement la performance:

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

Cependant, gardez à l'esprit que la création d'un index de 30 millions de lignes de la table peut prendre un certain temps.

The Impaler

2021-11-24 03:41:16

est-ce à dire disons que j'ai plusieurs cas une condition de requête, j'ai besoin de créer des index pour chaque cas? "(company_id, project_id, attribute_id, snapshot_ts)", "(project_id, attribute_id, snapshot_ts)", "(attribute_id, snapshot_ts)"

Sola

@sola si vous besoin de connaître indice optimal pour chaque requête, alors oui, cela peut être beaucoup d'indices. Mais vous pouvez probablement s'en tirer avec un peu moins optimal pour certains d'entre eux. Essayez un peu pour voir. Si vous avez des questions, assurez-vous d'inclure les EXPLIQUER (ANALYSER, TAMPONS)

jjanes

après la création de l'index sur ma machine locale, et il travail au début, mais après quelque temps, il n'a pas de déclenchement de l'index lors de la requête. Ce qui s'est passé sur le serveur de production ainsi.

Sola

@Sola Si la requête n'est pas à l'aide de l'index, puis à l'optimiseur il envisage un autre plan d'exécution. Tout d'abord, assurez-vous que les statistiques de la table sont à jour à l'aide de ANALYZE public.message. Ensuite, si le problème persiste, veuillez récupérer le plan d'exécution et l'ajouter à la question.

The Impaler

merci pour la réponse. Hier, lors de la tentative d'index (company_id, project_id, attribute_id, snapshot_ts), dans la condition à l'aide de (snapshot_ts et attribute_id), il travaille d'abord, puis, n'en ont pas. Maintenant, j'ai ajouter un autre indice (attribute_id, snapshot_ts), à cette fin, semblent à nouveau au travail, continuera de surveiller.

Sola

Postgres SQL de la requête lente avec une grande table (AWS RDS)

La question

La meilleure réponse

Dans d'autres langues

Cette page est dans d'autres langues

Populaires dans cette catégorie

Questions populaires dans cette catégorie