Büyük tabloyla Postgres SQL sorgusu yavaş (AWS RDS)

Question 1

Şu anda tablonun en az 30million satırı var ve SELECT sorgusu yapmaya çalıştığınızda büyüyor, son derece uzun sürüyor. Veritabanının performansını artırmadan önce sorguyu en iyi duruma getirmek için neye ihtiyacınız var?

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

Belirli bir sorgu göz önüne alındığında:

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

aşağıdaki endeks, performansı büyük ölçüde artırma potansiyeline sahiptir:

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

Ancak, 30 milyon satırlık bir tabloda dizin oluşturmanın biraz zaman alabileceğini unutmayın.

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

2

Belirli bir sorgu göz önüne alındığında:

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

aşağıdaki endeks, performansı büyük ölçüde artırma potansiyeline sahiptir:

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

Ancak, 30 milyon satırlık bir tabloda dizin oluşturmanın biraz zaman alabileceğini unutmayın.

The Impaler

2021-11-24 03:41:16

bu, birden fazla vaka sorgusu koşulum olduğunu varsayalım, her vaka için her dizini oluşturmam gerekiyor mu? "(company_id, project_id, attribute_id, snapshot_ts)"," (project_id, attribute_id, snapshot_ts)", " (attribute_id, snapshot_ts)"

Sola

@ sola her sorgu için en uygun dizine ihtiyacınız varsa, evet bu çok sayıda dizin olabilir. Ama muhtemelen bazıları için biraz daha az optimal olandan kurtulabilirsiniz. Birkaçını dene ve gör. Sorularınız varsa, AÇIKLA (ÇÖZÜMLE, ARABELLEKLERİ) eklediğinizden emin olun.

jjanes

yerel makinemde dizin oluşturduktan ve başlangıçta çalıştıktan sonra, ancak bir süre sonra sorgu yaparken dizinleri tetiklemedi. Bu, üretim sunucusunda da oldu.

Sola

@ Sola Sorgu dizini kullanmıyorsa, iyileştirici farklı bir yürütme planı düşünür. İlk olarak, tablonun istatistiklerinin güncel olduğundan emin olun ANALYZE public.message. Ardından, sorun devam ederse, lütfen yürütme planını alın ve soruya ekleyin.

The Impaler

teşekkür için cevap. Dün ındex (company_id, project_id, attribute_id, snapshot_ts) ile çalışırken, (snapshot_ts ve attribute_id) kullanarak, ilk başta çalışıyor, sonra çalışmıyor. Şimdi (attribute_id, snapshot_ts) ile başka bir dizin ekliyorum, bu amaçla tekrar çalışıyor gibi görünüyor, izlemeye devam edecek.

Sola

Büyük tabloyla Postgres SQL sorgusu yavaş (AWS RDS)

Soru

En iyi cevabı

Diğer dillerde

Bu sayfa diğer dillerde

Bu kategoride popüler

Popüler soruları bu kategoride